pull numbers from text string. trim text

[jacknc]

My cell a96 contains "$82 Million Cash Value $51.4 Million"

I need b96 to show 82000000 which I can do using =VALUE(TRIM(LEFT(A69,LEN(A69)-33)))*1000000 I also need c96 to show 51400000

But feel that may present problem if if cash value is less than 33 characters

The numbers can change - but the text "Million Cash Value" and "Million" will always be there....

Any suggestions?

 

[kvsrinivasamurthy]

in C96

=VALUE(MID(A96, FIND("$",A96,FIND("$",A96)+1)+1,FIND(" ",A96,FIND("$",A96,FIND("$",A96)+1)+1)- FIND("$",A96,FIND("$",A96)+1)-1))*10^6

 

[admiral100]

Hi,

If the text "Million Cash Value" and "Million" will always be there....

To find 82 B96 =LEFT(RIGHT(A96,LEN(A96)-1),FIND("Million Cash Value",A96)-2)*10^6

To find 51.4 C96
=SUBSTITUTE(MID(A96,FIND("Value",A96)+5,(FIND("Million",A96,FIND("Million",A96)+2))-(FIND("Value",A96)+5)),"$","")*10^6

 

[inactiveuserps07]

@jacknc, here are some additional suggestions:

B96:

=LEFT(A96,FIND(" ",A96))*10^6

C96:

=MID(SUBSTITUTE(A96,"Million",""),FIND("$",A96,2)-7,10)*10^6

 

[Rick Rothstein]

=MID(SUBSTITUTE(A96,"Million",""),FIND("$",A96,2)-7,10)*10^6

" >@C96:

=MID(SUBSTITUTE(A96,"Million",""),FIND("$",A96,2)-7,10)*10^6

=SUBSTITUTE(MID(A96,FIND(" $",A96),99),"Million","")*10^6

" >@
Three characters shorter...

=SUBSTITUTE(MID(A96,FIND(" $",A96),99),"Million","")*10^6

[Rick Rothstein]

Three characters shorter...

=SUBSTITUTE(MID(A96,FIND(" $",A96),99),"Million","")*10^6

Actually, this one is another 8 characters shorter yet, but it has the disadvantage of introducing a fourth function call. Of course, that may not be an actual disadvantage because I am not sure how a LEFT and LEN functions compare efficiency-wise to the SUBSTITUTE function that was replaced by them.

=MID(LEFT(A96,LEN(A96)-7),FIND(" $",A96),99)*10^6