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

[Rnkhch]

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!

 

[BSALV]

Map1
	A	B	C
1	Day25Timepoint5T12:43	Day 25	12:43
2	DayTimepoint5T12:44	no date	12:44
Blad3

Cell Formulas
Range	Formula
B1:B2	B1	=IFERROR("Day "& --SUBSTITUTE(MID(A1,4,2),"T",""),"no date")
C1:C2	C1	=RIGHT(A1,5)

 

[Rnkhch]

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.)

 

[BSALV]

like this ?

Map1
	A	B	C
1	Day25Timepoint5T12:43	Day 25	12:43
2	DayTimepoint5T12:44	no date	12:44
3	Day 5Timepoint5T12:43	Day 5	12:43
Blad1

Cell Formulas
Range	Formula
B1:B3	B1	=IFERROR("Day "& --SUBSTITUTE(MID(A1,4,2),"T",""),"no date")
C1:C3	C1	=RIGHT(A1,5)

 

[BSALV]

or like this

Map1
	A	B	C
1	Day25Timepoint5T12:43	Day 25	12:43
2	DayTimepoint5T12:44	no date	12:44
3	Day 5Timepoint5T12:43	Day 5	12:43
4	Day 25Timepoint5T12:44	Day 25	12:44
Blad1

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR("Day "& --SUBSTITUTE(SUBSTITUTE(MID(A1,4,3),"T",""),"i",""),"no date")
C1:C4	C1	=RIGHT(A1,5)

 

[Rnkhch]

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:

 

[BSALV]

Map2
	A	B	C
1	Day25Timepoint5T12:43	Day 25	12:43
2	DayTimepoint5T12:44	no day	12:44
3	Day 5Timepoint5T12:43	Day 5	12:43
4	Day 25Timepoint5T12:44	Day 25	12:44
5	Day 125Timepoint5T12:45	Day 125
6		no day
7
Blad1

Cell Formulas
Range	Formula
C1:C4	C1	=RIGHT(A1,5)
B1:B6	B1	=IFERROR("Day " &--TRIM(MID(SUBSTITUTE(A1,"Timepoint",REPT(" ",100)),4,4)),"no day")

 

[Rnkhch]

Awesome, thank you!