Formula to Pick Out Characters within a Text String
Try this:
For a text string with your posted general structure in cell A1
This formula returns the first number in the string:
B1: =--MID(LEFT(A1,FIND(" 2 ",A1)-1),FIND("$",A1)+1,255)
and this formula returns the second number in the string:
C1: =--MID(A1,FIND(" 2 $",A1)+4,255)
Copy those formulas down as far as you need.
Note: Both returned values are numeric.
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"Peanut" wrote:
I'm trying to create a formula that will pick out certain characters within a
text string, but the characters aren't a uniform length, nor are they always
the same character number within the string. For example, I would like to
pick out the dollar amounts in the following strings.
Distributed 1 $91.00 2 $419.00
Distributed 1 $1,980.00 2 $2,519.00
Distributed 1 $94,181.00 2 $42,014.00
I can use the MID function to pick out the first amount, but I can't figure
out how to pick out the last amount, since there are a differing number of
characters on each line.
Let me know if you have any secrets. I would even appreciate a macro that
would accomplish the same thing.
|