Forum Discussion
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.
5 Replies
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
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.
- Try this:
=IFERROR(CHOOSE(MATCH(LEFT(B5),
{“E”,”F”,”U”},0),
“TextForE”,”TextForF”,TextForU”),
LEFT(B5)) 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
