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

Deleted
Not applicable

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.

 

5 Replies

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

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

Thank you!

 

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

@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

Copy Your Function down

=IFERROR(VLOOKUP(LEFT(A7,1),{"E","Txt for E";"F","Txt for F";"U","Txt for U"},2,0),"")

Extract test.png

 

Hope that helps

Nabil Mourad

I have be trying to do this with a spreadsheet I prepare every month for ages but have never been able to figure it out. My letters are on the right - so I replaced left with right using your formula and it works like a dream.

 

Thank you so much - this has saved me so much time.