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
Highlighted

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

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 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
29 Replies