[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
carlsson
Newbie

Joined: 16 Mar 2012
Posts: 3

 Posted: Fri Mar 16, 2012 9:21 am    Post subject: [SOLVED] Extract data from string and format trouble 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
range
General User

Joined: 04 Jan 2012
Posts: 21

 Posted: Fri Mar 16, 2012 10:57 am    Post subject: 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))
carlsson
Newbie

Joined: 16 Mar 2012
Posts: 3

 Posted: Fri Mar 16, 2012 11:09 am    Post subject: That worked wonders, thank you!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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