Highlighted
Deleted
Not applicable

# EXCEL: "If cell contains specific letter using LEFT formula then return specific text"

Problem: 25k rows per column and I'm trying to extract different types of product references.

Using =Left(cell;1) I will have 3 different outputs. "E", "F" and "U".

Now what I need to do is rename everything that returns as E, F and U as their specific brand.

Tried using =(IF(LEFT(B5;1)="e";"text that I want to return";LEFT(B5;1))) and this works for E.

Now what do I do for it to include the cells that start with "f" and "u" within the same formula?

I tried some experiments with the formula OR but didn't achieve anything.

4 Replies
Highlighted

# Re: EXCEL: "If cell contains specific letter using LEFT formula then return specific text"

Hi @Deleted

Try below formula

=IF(LEFT(B5,1)="E","1st Text",IF(LEFT(B5,1)="F","2nd Text","3rd Text"))

Hope it will help.

Thanks

Tauqeer

Highlighted

# Re: EXCEL: "If cell contains specific letter using LEFT formula then return specific text"

Try this:
=IFERROR(CHOOSE(MATCH(LEFT(B5),
{“E”,”F”,”U”},0),
“TextForE”,”TextForF”,TextForU”),
LEFT(B5))
Highlighted

# Re: EXCEL: "If cell contains specific letter using LEFT formula then return specific text"

Thank you!

It worked fine, I guess I was overthinking a simple formula

Highlighted

# Re: EXCEL: "If cell contains specific letter using LEFT formula then return specific text"

@Deleted

Hi

I created a sample Excel file  for you

If your values to evaluate are in Column A then use this function  in Column B
=IFERROR(VLOOKUP(LEFT(A2,1),\$H\$2:\$I\$4,2,0),"")

Range H2 : I4 is the Table array of the VLOOKUP function

Then Hit F2 (edit Mode)

Select the Table array and Hit F9 >> Enter

You now hardcoded the Table array INSIDE the function

Delete the Table array