Formula to Pick Out Characters within a Text String
This works perfectly. Thanks for your help.
"T. Valko" wrote:
assuming "Distributed 1" is common to all strings and there are always 2 $
amounts:
For the first $ amount:
=LOOKUP(99^99,--MID(A1,16,ROW(INDIRECT("1:255"))))
For the second $ amount:
=MID(A1,FIND("$",A1,FIND("$",A1)+1)+1,255)+0
You'll have to format to get the decimal 0's back
Biff
"Peanut" wrote in message
...
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.
|