Naming cell based on string of another cell

[sahaider]

Hello,

I would like to extract specific string from a cell and based on that string would like type of different values in another cell.

Example, in cell A1 if we have 3.5 included as part of string then B1 =TEST1
In Cell A2 if we have 70 included as part of string B2=TEST2
in cell A3 if we 70/3.5 included as part of string then B3=TEST3

A1=18/18/21/26/3.5
A2=18/18/21/80/70
A3=18/18/21/26/70/3.5

Thanks.

 

[jtakw]

Hi,

Create a Lookup Table as shown in my sample I've used up to row 20 for the Table, just input additional specific string and desired result within the table as needed:

Book3.xlsx
	A	B	C	D	E
1	18/18/21/26/70/3.5	TEST3	TEST3	Lookup Table
2	18/18/21/26/3.5	TEST1	TEST1	3.5	TEST1
3	18/18/21/3.0/21	No Match	No Match	70	TEST2
4	18/18/21/80/70	TEST2	TEST2	70/3.5	TEST3
Sheet932

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(LOOKUP(2,1/FIND("/"&D$2:D$20&"/","/"&A1&"/"),E$2:E$20),"No Match")
C1:C4	C1	=IFERROR(LOOKUP(2,1/(FIND(D$2:D$20,A1)*(D$2:D$20<>"")),E$2:E$20),"No Match")

Edit: Added an alternate formula.