Extracting several values from a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Opinicus - 14 Mar 2007 14:42 GMT

I have a string that looks like this:

"5,789.93 YTL / 4,123.88 USD / 3,124.11 EUR"

The string's length and the numbers it contains will vary from day to day
and the numbers will not always have the same number of digits. (The last
being the problem that I can't get around.)

If the string is in A1, how do I put the YTL value in A2, the USD value in
A3, and the EUR value in A4?

Signature

Bob
http://www.kanyak.com

Reply to this Message

Don Guillett - 14 Mar 2007 15:05 GMT

You can use FIND to find YTL, USD, EUR, and then use within a MID formula

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have a string that looks like this:
>
[quoted text clipped - 6 lines]
> If the string is in A1, how do I put the YTL value in A2, the USD value in
> A3, and the EUR value in A4?

Reply to this Message

Bernie Deitrick - 14 Mar 2007 15:32 GMT

Bob,

Assuming that your string DOESN'T have the quotes around it:

A2 formula
=VALUE(LEFT(A1,FIND("YTL",A1)-2))

A3 formula
=VALUE(LEFT(MID(A1,FIND("YTL / ",A1)+6,LEN(A1)),FIND(" USD",MID(A1,FIND("YTL / ",A1)+6,LEN(A1)))-1))

A4 formula
=VALUE(LEFT(MID(A1,FIND("USD / ",A1)+6,LEN(A1)),FIND(" EUR",MID(A1,FIND("USD / ",A1)+6,LEN(A1)))-1))

IF it does have the quotes, use this formula

=SUBSTITUTE(A1,"""","")

in place of every A1 reference, or in another cell that you then use in the above formulas....

HTH,
Bernie
MS Excel MVP

>I have a string that looks like this:
>
[quoted text clipped - 5 lines]
> If the string is in A1, how do I put the YTL value in A2, the USD value in A3, and the EUR value
> in A4?

Reply to this Message

Opinicus - 14 Mar 2007 17:05 GMT

>>I have a string that looks like this:
>> "5,789.93 YTL / 4,123.88 USD / 3,124.11 EUR"
[quoted text clipped - 3 lines]
>> If the string is in A1, how do I put the YTL value in A2, the USD value
>> in A3, and the EUR value in A4?

> Assuming that your string DOESN'T have the quotes around it:
> A2 formula
[quoted text clipped - 5 lines]
> =VALUE(LEFT(MID(A1,FIND("USD / ",A1)+6,LEN(A1)),FIND("
> EUR",MID(A1,FIND("USD / ",A1)+6,LEN(A1)))-1))

Excellent! Many thanks!

Signature

Bob
http://www.kanyak.com

Reply to this Message

Lori - 14 Mar 2007 16:17 GMT

Select column A, then try: Data > Text to Columns:

1. Delimited > Next.
2. Space delimiter >Next
3. Set non-numeric fields to Skip and Destination B1 > Finish

> I have a string that looks like this:
>
[quoted text clipped - 9 lines]
> --
> Bobhttp://www.kanyak.com

Reply to this Message

RichardSchollar - 14 Mar 2007 16:24 GMT

Hi Bob

You can place the following in A2 and copy down to A4:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E$16," YTL / "," "),"
USD / "," ")," ",REPT(" ",200)),1+(ROWS($A$1:$A1)-ROW($A$1))*200,100))
+0

Hope this helps!

Richard

> I have a string that looks like this:
>
[quoted text clipped - 9 lines]
> --
> Bobhttp://www.kanyak.com

Reply to this Message

RichardSchollar - 14 Mar 2007 16:26 GMT

Sorry - wrong cell ref in there - should have been:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1," YTL / "," ")," USD /
"," ")," ",REPT(" ",200)),1+(ROWS($A$1:$A1)-ROW($A$1))*200,100))+0

On 14 Mar, 15:24, "RichardSchollar" <RichardSchol...@googlemail.com>
wrote:

> Hi Bob
>
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Reply to this Message

Opinicus - 14 Mar 2007 17:06 GMT

>I have a string that looks like this:
> "5,789.93 YTL / 4,123.88 USD / 3,124.11 EUR"
[quoted text clipped - 3 lines]
> If the string is in A1, how do I put the YTL value in A2, the USD value in
> A3, and the EUR value in A4?

Thanks to everyone who took the trouble to reply. As usual, Excel provides
many different paths to the same goal and all the suggested solutions were
interesting and instructive.

Signature

Bob
http://www.kanyak.com

Reply to this Message