please see attached file. Or Link below.
I need an automated way to extract the dollar amount from B and then from C and put them into separate columns.
So the dollar amount in column B is extracted out of the text and then put into a separate column. Then, the dollar amount in column C is extracted and put into a separate column.
Kind Thanks,
Eddie
Last edited by JBeaucaire; 08-30-2012 at 10:30 PM.
=INT(LOOKUP(1E+100,--MID(SUBSTITUTE(SUBSTITUTE(B2,"/","x"),"per","x"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(SUBSTITUTE(B2,"/","x"),"per","x")&"0123456789")),ROW(INDIRECT("1:99")))))
Guys, do you have brain only on head or all over the body ? It is amzing !!!!!!!!
D2: =TRIM(LEFT(SUBSTITUTE(REPLACE(B2,1,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},B2&1234567890))-1,""), " ", REPT(" ", 99)),2))
C2: =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(C2,1,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},C2&1234567890))-1,""), " ", REPT(" ", 99)),2),"/",""),".",""))
Copy down.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.
indeed it is solved...thank you all so very much. y'all are awesome.
Eddie
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks