• 411K Members
• 5,813 Online
• 466K Conversations

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

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

# 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

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

Hope that helps

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies