• 514K Members
• 2,598 Online
• 611K Conversations

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

# 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

# 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))

# 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

# 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

Copy Your Function down

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

Hope that helps

Related Conversations
Problems with formating text within a cell.
Maciej Fox in Excel on
10 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies