I want to extract a substring from a cell in a column based on search match

[andrebooyzen]

Hi All.
Great forum.
I have a column of data and I want to search the whole column for a match to name and then extract the first 5 characters from the cell that has a match. I have triend INDEX and MATCH and VLOOKUP with varying results.
Please help
Thanks

Typical cell in the column looks like this

09:00-10h30
Andre
Peter

I want to search all the cells in a specified column for a name eg Peter, and if it finds a match then return the first 5 characters of the cell.

 

[DanteAmor]

Hi @andrebooyzen.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try this formula:

Dante Amor
	A	B	C	D	E
1					match to name
2					Peter
3
4		09:00-10h30 Andre Peter	09:00
5		09:00-10h30 Sm John
6		09:00-10h30 Wa Peter	09:00
Hoja4

Cell Formulas
Range	Formula
C4:C6	C4	=IF(ISNUMBER(SEARCH($E$2,B4)),LEFT(B4,5),"")

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[andrebooyzen]

Thanks a lot Dante. That will work perfectly if searching one cell. But I want to search the whole column until it finds a match for Peter and then extract the first 5 characters from the cell it matched with.
Appreciate your help.

 

[andrebooyzen]

Thanks a lot Dante. That will work perfectly if searching one cell. But I want to search the whole column until it finds a match for Peter and then extract the first 5 characters from the cell it matched with.
Appreciate your help.

I want to do this as a straight excel formula

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

See if this is headed in the right direction

23 05 31.xlsm
	A	B	C
1
2		11:30-12h30 Andre Pedro
3		09:00-10h30 Simon John
4		09:00-10h30 Wayne Peter
5
6
7
8		Name	Time
9		Peter	09:00
10		Andre	11:30
11		Ken
12		Wayne	09:00
Match

Cell Formulas
Range	Formula
C9:C12	C9	=LET(x,XLOOKUP("*"&B9&"*",B$2:B$4,B$2:B$4,"",2),IF(x="",x,LEFT(x,5)+0))

 

[DanteAmor]

Try:

varios 30may2023.xlsm
	A	B	C	D	E
1				match to name
2				peter	09:00
3
4		06:00-10h30 Fi Sue
5		08:00-10h30 Sm John
6		09:00-10h30 Wa Peter
Hoja4

Cell Formulas
Range	Formula
E2	E2	=IFERROR(LEFT(INDEX(B4:B6,MATCH("*"&D2&"*",B4:B6,0)),5),"Not exists")

 

[Peter_SSs]

See if this is headed in the right direction

If you don't need the result as an actual time (number) then the version of my suggestion would be

23 05 31.xlsm
	A	B	C
1
2		11:30-12h30 Andre Pedro
3		09:00-10h30 Simon John
4		09:00-10h30 Wayne Peter
5
6
7
8		Name	Time
9		Peter	09:00
10		Andre	11:30
11		Ken
12		Wayne	09:00
Match (2)

Cell Formulas
Range	Formula
C9:C12	C9	=LET(r,B$2:B$4,LEFT(XLOOKUP("*"&B9&"*",r,r,"",2),5))

 

[DanteAmor]

Also try the following:

Dante Amor
	A	B	C	D	E
1				match to name
2				peter	09:00
3
4		06:00-10h30 Fi Sue
5		08:00-10h30 Sm John
6		09:00-10h30 Wa Peter
Hoja4

Cell Formulas
Range	Formula
E2	E2	=LEFT(VLOOKUP("*"&D2&"*",B4:B6,1,0),5)

 

[andrebooyzen]

=LET(x,XLOOKUP("*"&B9&"*",B$2:B$4,B$2:B$4,"",2),IF(x="",x,LEFT(x,5)+0))

Thanks Peter and for the welcome.
It found 2 matches correctly in the column I searched. However it returned a 1 in one match and a 0 in the other match.

 

[andrebooyzen]

=LEFT(VLOOKUP("*"&D2&"*",B4:B6,1,0),5)

Thanks Dante
Works perfectly
However, when there is no match it returns a #N/A. Any way I can get rid of this?