I have an Excel worksheet that includes 3 different numbers in a single cell along with specific text as shown in this spreadsheet:
Excel Sample 2016-01-13.xlsx
I need to put the 1st, 2nd, and 3rd numbers in different cells (in columns G, H, and I, respectively).
I'd really appreciate any help.
Thanks!
Rob
In G2 Cell
=IFERROR(LEFT($F2,FIND(" ",$F2)-1),"")
In H2 Cell
=IFERROR(TRIM(LEFT(MID($F2,FIND("redactions on ",$F2)+LEN("redactions on "),255),FIND(" ",TRIM(MID($F2,FIND("redactions on ",$F2)+LEN("redactions on "),255))))),"")
In I2 Cell
=IFERROR(TRIM(LEFT(TRIM(MID(MID($F2,FIND("redactions on ",$F2)+LEN("redactions on "),255),FIND("redactions on ",MID($F2,FIND("redactions on ",$F2)+LEN("redactions on "),255))+LEN("redactions on "),255)),FIND(" ",TRIM(MID(MID($F2,FIND("redactions on ",$F2)+LEN("redactions on "),255),FIND("redactions on ",MID($F2,FIND("redactions on ",$F2)+LEN("redactions on "),255))+LEN("redactions on "),255)))-1)),"")
Drag all the formula's down
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Try
G2=IFERROR(LEFT(F2,FIND(" ",F2)-1),"")+0
L2=LEFT(MID(F2,FIND("on ",F2)+3,250),FIND(" ",MID(F2,FIND("on ",F2)+3,250))-1)+0
M2=LEFT(TRIM(RIGHT(SUBSTITUTE(F2," ",REPT(" ",50)),101)),FIND(" ",TRIM(RIGHT(SUBSTITUTE(F2," ",REPT(" ",50)),101)))-1)+0
Drag down all the formula as per your requirement.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Sixthsense, you rock! Thank you!
Quick add-on question (because frankly, I don't understand what your 2nd and 3rd formulas are doing at all):
If I added the following text to the very end of some of the cells in column F: Excerpt(X), where X is another number, would it screw up either of your two other formulas? How would I add the number X to yet another cell?
Thanks again! You saved me--I think the rest of my hair might be safe for tonight (I can stop pulling it out
Rob
I hope shukla.ankur281190 will cover your addition since his approach is smarter than my suggested solution.Originally Posted by robbyvegas
@Sixthsense Guru thanks for appreciate my solution.
@Rob can you put some example here so that i can understand what you actually want to do further.
Thanks
Ankur Shukla
Thanks! I already put in most of @Sixthsense 's solution, but I'm happy to change it. Here is a sample.
Thanks again!Excel Sample 2016-01-13.xlsx
Try
H2=IFERROR(TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(F2,"(",REPT(" ",50)),50)),")",""))+0,0)
Formula:
Please Login or Register to view this content.
Awesome. Perfect. Thank you!
Thanks for feedback and Rep !!!
Please mark as it solved now.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks