How to extract a phrase from cells only if the keyword occurs with an adjacent number?

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
528
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a column of data entered rather sloppily with cells that look something like this:

Day25Timepoint5T12:43 (meant to be 'Day 2 Timepoint 5 Time 12:43')

My goal is to extract the "day" information from these cells along with the number of the day. So for the example above, I would need "Day 25" to be extracted form that cell. But also I want the formula to work only if there is a number immediately after the word "Day", and I want the number to have 1 space from the the word "Day" if that 1 space already isn't there.

I was having a hard time coming up with a function 😅 Thanks for any input!
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Map1
ABC
1Day25Timepoint5T12:43Day 2512:43
2DayTimepoint5T12:44no date12:44
Blad3
Cell Formulas
RangeFormula
B1:B2B1=IFERROR("Day "& --SUBSTITUTE(MID(A1,4,2),"T",""),"no date")
C1:C2C1=RIGHT(A1,5)
 
Upvote 0
Thanks. Just one thing I noticed is that if there a space already between "Day" and the number, then your formula doesn't work? (I got "Day 2" when I inserted a a space.)
 
Upvote 0
like this ?
Map1
ABC
1Day25Timepoint5T12:43Day 2512:43
2DayTimepoint5T12:44no date12:44
3Day 5Timepoint5T12:43Day 512:43
Blad1
Cell Formulas
RangeFormula
B1:B3B1=IFERROR("Day "& --SUBSTITUTE(MID(A1,4,2),"T",""),"no date")
C1:C3C1=RIGHT(A1,5)
 
Upvote 0
or like this
Map1
ABC
1Day25Timepoint5T12:43Day 2512:43
2DayTimepoint5T12:44no date12:44
3Day 5Timepoint5T12:43Day 512:43
4Day 25Timepoint5T12:44Day 2512:44
Blad1
Cell Formulas
RangeFormula
B1:B4B1=IFERROR("Day "& --SUBSTITUTE(SUBSTITUTE(MID(A1,4,3),"T",""),"i",""),"no date")
C1:C4C1=RIGHT(A1,5)
 
Upvote 0
That's better. I just noticed that with three-digit numbers it doesn't work if there's a space, but it works with one or two digits.

My XL2BB stopped working (Excel blocked it 😭 ) so I'm attaching the picture here:

Screenshot (48).png
 
Upvote 0
Map2
ABC
1Day25Timepoint5T12:43Day 2512:43
2DayTimepoint5T12:44no day12:44
3Day 5Timepoint5T12:43Day 512:43
4Day 25Timepoint5T12:44Day 2512:44
5Day 125Timepoint5T12:45Day 125
6no day
7
Blad1
Cell Formulas
RangeFormula
C1:C4C1=RIGHT(A1,5)
B1:B6B1=IFERROR("Day " &--TRIM(MID(SUBSTITUTE(A1,"Timepoint",REPT(" ",100)),4,4)),"no day")
 
Upvote 0
Solution

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top