Extracting several values from a string

[Opinicus]

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?

 

[Don Guillett]

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

 

[Bernie Deitrick]

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

 

[Lori]

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

 

[RichardSchollar]

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

 

[RichardSchollar]

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

 

[Opinicus]

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))

Excellent! Many thanks!

 

[Opinicus]

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?

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.