| View previous topic :: View next topic |
| Author |
Message |
foxy
Power User
Joined: 10 Mar 2005
Posts: 82
|
 Posted: Sat Aug 27, 2005 4:08 am Post subject: Using MID... help needed! |
 |
|
I've got a data series, where date and the value are in one cell (I have copied in from a web page). I used MID function to split it. However when I copy the data as numeber, I have blank cell. If I copy it as a number and string, I've got (') before every number and I cannot get rid of it using Find & Replace.
Is there any way either to pase MID results as numbers or get rid of this (') to convert them into numbers?
_________________
Using Ooo 2.2 on Ubuntu Feisty |
|
| Back to top |
|
 |
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Sat Aug 27, 2005 4:44 am Post subject: Re: Using MID... help needed! |
|
|
| foxy wrote: | | Is there any way either to pase MID results as numbers or get rid of this (') to convert them into numbers? |
Use the VALUE() function to convert the text returned by MID() into a number.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!
OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos |
|
| Back to top |
|
|
foxy
Power User
Joined: 10 Mar 2005
Posts: 82
|
| Posted: Sat Aug 27, 2005 4:59 am Post subject: Re: Using MID... help needed! |
|
|
| Dale wrote: | | foxy wrote: | | Is there any way either to pase MID results as numbers or get rid of this (') to convert them into numbers? |
Use the VALUE() function to convert the text returned by MID() into a number. |
thanks a lot! I have not thought about it....
_________________
Using Ooo 2.2 on Ubuntu Feisty |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Sat Aug 27, 2005 5:14 am Post subject: |
|
|
Text to numbers. Quotiing myself from over here http://www.oooforum.org/forum/viewtopic.phtml?t=22952 | Quote: | Try this and see if it works for you. Select the column of text numbers and do a Find & Replace. Put ^. in the Find box, & in the Replace box and check the Regular Expression and Current Selection Only boxes.
If it does work, don't ask me why.
Actually it appears that you don't need to select the column and it will leave other text and formulas alone but I haven't tested that enough to be sure it's safe. BTW, you can record a macro of this, just set the Find and Replace dialog up then record the click on the Replace All button. |
|
|
| Back to top |
|
|
foxy
Power User
Joined: 10 Mar 2005
Posts: 82
|
| Posted: Sat Aug 27, 2005 5:25 am Post subject: |
|
|
| JohnV wrote: | Text to numbers. Quotiing myself from over here http://www.oooforum.org/forum/viewtopic.phtml?t=22952 | Quote: | Try this and see if it works for you. Select the column of text numbers and do a Find & Replace. Put ^. in the Find box, & in the Replace box and check the Regular Expression and Current Selection Only boxes.
If it does work, don't ask me why.
Actually it appears that you don't need to select the column and it will leave other text and formulas alone but I haven't tested that enough to be sure it's safe. BTW, you can record a macro of this, just set the Find and Replace dialog up then record the click on the Replace All button. |
|
thanks for that too.. I will keep this in mind since it's a simpler solution....
_________________
Using Ooo 2.2 on Ubuntu Feisty |
|
| Back to top |
|
|
|
