convert calculated cell into numeric for copying

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

I am reading the latitude and longitude off the web for different places.

The number comes is as "35.990,-81.336". I use the Left() and Right() commands to take the number apart and put the results into two separate cells.

Now I need to cut and paste the contents of the cells into another web page. Trouble is, the pasted item looks like "=left()", not 35.990, and the web page won't do its thing.

How can I convert the =left() cell to look like a simple number?

Thanks

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

Hallo
Use instead the Formula : →Data→Text to Columns with comma as Fielddelimiter

Karo

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

Karolus, I am not sure what you mean. Maybe I didn't explain it clearly. I am not importing a csv file and selecting the field limiters.

Within one spreadsheet I am taking a compound number apart and put each piece in its own cell. Then I want to cut and paste each number into another document.

When I do that, I get the formula for that cell, not the number in the cell. i.e.," = left()", not 36.216. The formula makes no sense to the second document.

I need some way of making the computed numbers be numbers, not formulas.

Villeroy · Posted: Thu Jul 12, 2012 7:42 am Post subject:

Why do you put the numbers in one cell? Put them in two cells in the first place.

Data>Text2Columns can fix the mistake belatedly.

VALUE may convert a text into some number.

No matter what you do, you should be aware that the text "1.234" means something completely different when evaluated in different locale context. In English locale context it evaluates to one point two-three-four. Many world regions use a comma as decimal separator and dot as thousands separator. In such a locale context "1.234" evaluates to one-thousand-two-hundred-thirty-four.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

The two numbers run together come from a website that gives latitude and longitude when you type in the name of a city.

They don't present them as two different numbers, just the pair run together.

I have to copy and paste these as separate numbers on a second website.

In the mean time, I record them in my OO spreadsheet. That is where I would like to take the numbers apart, so I can cut and paste them one at a time to the second website.

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

If I take one of the converted numbers, i.e., "left()" and Paste Special (text) into a cell, I get
'35.678, with the apostrophe ['] in front.

If I cut and paste this to the website, it uses it correctly, ignoring the ['].

However, I have to manually cut and Paste Special the two numbers, resetting the paste options when they aren't right.

Is there any way make a macro do this?

Villeroy · Posted: Thu Jul 12, 2012 9:07 am Post subject:

Yes, sure. This is expected behaviour when you paste text you will get text.

A web-page has no numbers nor formulas. Everything on a web-page is a stream of charcters made for human reading.
Anything in a spreadsheet cell evaluates to either text or number.

Once again:
Why do start with 2 numbers in one cell?
What is wrong with Text2Columns?
What is wrong with function VALUE?
Which number is the text "35.678" supposed to be? A decimal between 35 and 36 or an integer between 35000 and 36000?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

Villeroy,

I don't start with 2 numbers in one cell. I start with a website that has two numbers in one cell. I copy the dual numbers out of the website and paste them into my SS. That is how the two get into the same cell in OO.calc.

Then I have to deal with them. I have to take them apart so I can post them individually into another website, which has two cells for the information.

I can do this manually with no problem. I am trying to get the SS to do the work for me.

I understand the US and Euro differences in number represenatation, but that is not an issue here, unless it is an issue internal to OO.calc.

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

I see I didn't answer all your questions.

I can't find a Text2Columns command in the menus. Is this a macro command?

When I use the value command, I get a formula in the cell, which will not cut and paste into the web site as a number.

The number "35.678" is a three decimal value, per the US convention.

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

BasicGuy · General User Joined: 30 Jun 2009 Posts: 37

OK, I see it now. It is grayed out and I didn't notice it.

OK, sorry for the confusion. That works.

If now I can figure a keystroke to execute it.