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

[SOLVED] Extract data from string and format trouble

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


Joined: 16 Mar 2012
Posts: 3

PostPosted: Fri Mar 16, 2012 9:21 am    Post subject: [SOLVED] Extract data from string and format trouble Reply with quote

Hi, I am a newbie having trouble...
I'm trying to manipulate data imported from a website that is updated every few minutes (that works great using the Import external data feature).
What I want to do is to convert currency and then calculate the price.


For example, the price is displayed in Australian Dollars in cell A1 like this : 3.15 AUD
I use =LEFT(A1;4) to extract the price without the AUD part, and then put the extracted price in another cell (B1)

I want to convert that into US dollars, so in cell C1 I have the exchange rate 1 AUD = 1.0581 U.S. dollar (that is also imported and updated every now and then).
To extract that value into cell D1 I use =MID(C1;9;5). That only works as long as there is 4 decimals in the exchange rate, if the exchange rate is 1.04 U.S. dollar it would extract "1.04 U"

Is there a way to just extract the number?


I am also having trouble with data formats. I set the format in cells B1 and D1 to numbers and with 2 decimals, but somehow it seems to be interpreted as text as I can't seem to use the numbers in a function.

Could someone point me in the right direction?



edit Ok, I've figured out a solution to my decimal problem.
I used =LEN() to get the number of characters in the string and then just subtracted the number of characters that I didn't want (i.e remove 1 AUS = and U.S dollar) like this: =MID(C1;9;LEN(C1)-20))


Last edited by carlsson on Sat Mar 17, 2012 3:14 am; edited 1 time in total
Back to top
View user's profile Send private message
range
General User
General User


Joined: 04 Jan 2012
Posts: 21

PostPosted: Fri Mar 16, 2012 10:57 am    Post subject: Reply with quote

Hello,

If you enclose your formula with:VALUE(), it will convert the string to a numeric value so that it can be used in calculations, eg

=VALUE(MID(C1;9;LEN(C1)-20))
Back to top
View user's profile Send private message
carlsson
Newbie
Newbie


Joined: 16 Mar 2012
Posts: 3

PostPosted: Fri Mar 16, 2012 11:09 am    Post subject: Reply with quote

That worked wonders, thank you! Smile
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