Naming cell based on string of another cell

sahaider

New Member
Joined
May 30, 2014
Messages
34
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,098
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
118/18/21/26/70/3.5TEST3TEST3Lookup Table
218/18/21/26/3.5TEST1TEST13.5TEST1
318/18/21/3.0/21No MatchNo Match70TEST2
418/18/21/80/70TEST2TEST270/3.5TEST3
Sheet932
Cell Formulas
RangeFormula
B1:B4B1=IFERROR(LOOKUP(2,1/FIND("/"&D$2:D$20&"/","/"&A1&"/"),E$2:E$20),"No Match")
C1:C4C1=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.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,254
Members
425,460
Latest member
Astros1243

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
Top