I want split number in STRING below in two different column. Right now only able to extract one.
Column B6 = "Buy only when USD50 and not over USD70"
Column C6 Formula =LOOKUP(99^99,--("0"&MID(B6,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B6&"0123456789")),ROW($1:$10000))))
Column C6 Answer = 50
Column D6 Formula =LOOKUP(99^99,--("0"&MID(B6,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B6&"0123456789")),ROW($1:$10000))))
Column D6 Answer = 70
I also want to 70 but cant figure out the right. Can somebody help. Thanks
Try this in cell D6:
=LOOKUP(99^99,--("0"&MID(MID(B6,SEARCH(C6,B6)+3,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(B6,SEARCH(C6,B6)+3,99)&"0123456789")),ROW($1:$10000))))
Edit: Sorry of that's overly long. I do really understand how that formula works. All I did was replace B6 with the end of the string following the answer in C6. So basically, MID(B6,SEARCH(C6,B6)+3,99) just searches for the number in C6 and provides everything that follows that number for the original formula to use.
Edit2: Of course, if the last number was always a 2 digit number at the end, then simply: =RIGHT(B6,2)
Last edited by Beamernsw; 02-21-2016 at 12:52 AM.
Hey there! Thank you very much. Appreciate your helpOriginally Posted by Beamernsw
Try this in cell D6:
=LOOKUP(99^99,--("0"&MID(MID(B6,SEARCH(C6,B6)+3,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(B6,SEARCH(C6,B6)+3,99)&"0123456789")),ROW($1:$10000))))
Edit: Sorry of that's overly long. I do really understand how that formula works. All I did was replace B6 with the end of the string following the answer in C6. So basically, MID(B6,SEARCH(C6,B6)+3,99) just searches for the number in C6 and provides everything that follows that number for the original formula to use.
Edit2: Of course, if the last number was always a 2 digit number at the end, then simply: =RIGHT(B6,2)
Here is how to do this with one formula
Enter formula in C6 and pull it to the right
Formula:
=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B6,"USD",REPT(" ",50)),50*COLUMNS($B:B),50))," ",REPT(" ",15)),15))+0
v B C D 6 Buy only when USD50 and not over USD70 50 70
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks