OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Using MID... help needed!

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
foxy
Power User
Power User


Joined: 10 Mar 2005
Posts: 82

PostPosted: Sat Aug 27, 2005 4:08 am    Post subject: Using MID... help needed! Reply with quote

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
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Sat Aug 27, 2005 4:44 am    Post subject: Re: Using MID... help needed! Reply with quote

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
View user's profile Send private message
foxy
Power User
Power User


Joined: 10 Mar 2005
Posts: 82

PostPosted: Sat Aug 27, 2005 4:59 am    Post subject: Re: Using MID... help needed! Reply with quote

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
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sat Aug 27, 2005 5:14 am    Post subject: Reply with quote

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
View user's profile Send private message
foxy
Power User
Power User


Joined: 10 Mar 2005
Posts: 82

PostPosted: Sat Aug 27, 2005 5:25 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group