I'm using Excel 2010, just in case.
I have a cell that has combined data like this: "45 Days, 12 Hrs, 35 Mins" in column A.
In column B I would like to extract the numeric value of "45", then in column C I would like to extract the numeric value of "12", and the in column D, extract the numeric value of "35". Can anyone help me out with this?
Enter formula in B1 and drag it across and down (if needed)
Formula:
Please Login or Register to view this content.
v A B C D 1 45 Days, 12 Hrs, 35 Mins 45 12 35
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
this is my attempt...
in B1, =LEFT(A1,SEARCH("days",A1)-2)
in C1, =MID(A1,SEARCH("hrs",A1)-3,2)
in D1, =MID(A1,SEARCH("mins",A1)-3,2)
adjust to your range.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Alkey, thank you so much... That was perfect!
Hi,
This will also work, just a little shorter:
Excel 2016 (Windows) 64 bit
45 Days, 12 Hrs, 35 Mins 45 12 35
Sheet: Sheet140
Excel 2016 (Windows) 64 bit
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),COLUMNS($B1:B1)*200-199,100))
Sheet: Sheet140
Formula copied across.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks