Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-784265%22%20slang%3D%22en-US%22%3EEXCEL%3A%20%22If%20cell%20contains%20specific%20letter%20using%20LEFT%20formula%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784265%22%20slang%3D%22en-US%22%3E%3CP%3EProblem%3A%2025k%20rows%20per%20column%20and%20I'm%20trying%20to%26nbsp%3Bextract%20different%20types%20of%20product%20references.%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20%3DLeft(cell%3B1)%20I%20will%20have%203%20different%20outputs.%20%22E%22%2C%20%22F%22%20and%20%22U%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20what%20I%20need%20to%20do%20is%20rename%20everything%20that%20returns%20as%20E%2C%20F%20and%20U%20as%20their%20specific%20brand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETried%20using%26nbsp%3B%3CU%3E%3D(IF(LEFT(B5%3B1)%3D%22e%22%3B%22text%20that%20I%20want%20to%20return%22%3BLEFT(B5%3B1)))%3C%2FU%3Eand%20this%20works%20for%20E.%3C%2FP%3E%3CP%3ENow%20what%20do%20I%20do%20for%20it%20to%20include%20the%20cells%20that%20start%20with%20%22f%22%20and%20%22u%22%26nbsp%3Bwithin%20the%20same%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20some%20experiments%20with%20the%20formula%20OR%20but%20didn't%20achieve%20anything.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-784265%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784294%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%3A%20%22If%20cell%20contains%20specific%20letter%20using%20LEFT%20formula%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784294%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385835%22%20target%3D%22_blank%22%3E%40henriqueboap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20below%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3DIF(LEFT(B5%2C1)%3D%22E%22%2C%221st%20Text%22%2CIF(LEFT(B5%2C1)%3D%22F%22%2C%222nd%20Text%22%2C%223rd%20Text%22))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20will%20help.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784364%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%3A%20%22If%20cell%20contains%20specific%20letter%20using%20LEFT%20formula%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784364%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DIFERROR(CHOOSE(MATCH(LEFT(B5)%2C%3CBR%20%2F%3E%7B%E2%80%9CE%E2%80%9D%2C%E2%80%9DF%E2%80%9D%2C%E2%80%9DU%E2%80%9D%7D%2C0)%2C%3CBR%20%2F%3E%E2%80%9CTextForE%E2%80%9D%2C%E2%80%9DTextForF%E2%80%9D%2CTextForU%E2%80%9D)%2C%3CBR%20%2F%3ELEFT(B5))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785452%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%3A%20%22If%20cell%20contains%20specific%20letter%20using%20LEFT%20formula%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785452%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20fine%2C%20I%20guess%20I%20was%20overthinking%20a%20simple%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785492%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%3A%20%22If%20cell%20contains%20specific%20letter%20using%20LEFT%20formula%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385835%22%20target%3D%22_blank%22%3E%40henriqueboap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20created%20a%20sample%20Excel%20file%26nbsp%3B%20for%20you%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%20your%20values%20to%20evaluate%20are%20in%20Column%20A%20then%20use%20this%20function%26nbsp%3B%20in%20Column%20B%3CBR%20%2F%3E%3CSTRONG%3E%3DIFERROR(VLOOKUP(LEFT(A2%2C1)%2C%24H%242%3A%24I%244%2C2%2C0)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ERange%20H2%20%3A%20I4%20is%20the%20Table%20array%20of%20the%20VLOOKUP%20function%3C%2FP%3E%3CP%3EThen%20Hit%20F2%20(edit%20Mode)%3C%2FP%3E%3CP%3ESelect%20the%20Table%20array%20and%20Hit%20F9%20%26gt%3B%26gt%3B%20Enter%3C%2FP%3E%3CP%3EYou%20now%20hardcoded%20the%20Table%20array%20INSIDE%20the%20function%3C%2FP%3E%3CP%3E%3CSTRONG%3EDelete%20the%20Table%20array%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECopy%20Your%20Function%20down%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(VLOOKUP(LEFT(A7%2C1)%2C%7B%22E%22%2C%22Txt%20for%20E%22%3B%22F%22%2C%22Txt%20for%20F%22%3B%22U%22%2C%22Txt%20for%20U%22%7D%2C2%2C0)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125388iBD789AE465D24011%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Extract%20test.png%22%20title%3D%22Extract%20test.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

 

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

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

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