SOLVED

Location name if the cell begins with different text

%3CLINGO-SUB%20id%3D%22lingo-sub-3181493%22%20slang%3D%22en-US%22%3ELocation%20name%20if%20the%20cell%20begins%20with%20different%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3181493%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20excel%20sheet%20with%2050000%20rows%20of%20global%20data.%20I%20want%20to%20populate%20the%20Country%20by%20checking%20Name%20column%20if%20it%20begins%20with%20ABC1GB%20then%20Country%20is%20UK%2C%20if%20begins%20with%20ABC1US%20then%20Country%20is%20America%2C%20if%20beings%20with%20ABC1AU%20then%20Country%20is%20Australia%2C%20if%20beings%20with%20ABC1IN%20then%20Country%20is%20India.%20I%20want%20to%20populate%20Location%20by%20checking%20if%20the%20IP%20is%20between%2010.224.5.1%20and%2010.224.5.254%20then%20Location%20is%20London%20and%20so%20on.%20Please%20suggest%20the%20best%20approach%20to%20achieve%20this.%3C%2FP%3E%3CTABLE%20width%3D%22268%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2270%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2270%22%3EIP%20Address%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ECountry%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ELocation%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABC1GBM%3C%2FTD%3E%3CTD%3E10.224.5.7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABC1USM%3C%2FTD%3E%3CTD%3E10.227.8.9%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABC1AUM%3C%2FTD%3E%3CTD%3E10.226.5.4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABC1INM%3C%2FTD%3E%3CTD%3E10.221.4.3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3181493%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3181646%22%20slang%3D%22en-US%22%3ERe%3A%20Location%20name%20if%20the%20cell%20begins%20with%20different%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3181646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1310764%22%20target%3D%22_blank%22%3E%40excelinLife%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(LEFT(A2%2C6)%3D%22ABC1GB%22%2C%22UK%22%2CIF(LEFT(A2%2C6)%3D%22ABC1US%22%2C%22USA%22%2CIF(LEFT(A2%2C6)%3D%22ABC1IN%22%2C%22India%22%2CIF(LEFT(A2%2C6)%3D%22ABC1AU%22%2C%22Australia%22%2C%22%22))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20entered%20this%20formula%20in%20C2%20and%20copied%20down.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(LEFT(B2%2C9)%3D%2210.224.5.%22%2CAND(NUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26lt%3B%3D254))%2C%22London%22%2CIF(AND(LEFT(B2%2C9)%3D%2210.227.8.%22%2CAND(NUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26lt%3B%3D254))%2C%22Washington%22%2CIF(AND(LEFT(B2%2C9)%3D%2210.226.5.%22%2CAND(NUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26lt%3B%3D254))%2C%22Canberra%22%2CIF(AND(LEFT(B2%2C9)%3D%2210.221.4.%22%2CAND(NUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26lt%3B%3D254))%2C%22New-Dehli%22%2C%22%22))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20entered%20this%20formula%20in%20D2%20and%20copied%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20there%20might%20be%20more%20advanced%20solutions%20for%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3181763%22%20slang%3D%22en-US%22%3ERe%3A%20Location%20name%20if%20the%20cell%20begins%20with%20different%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3181763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1310764%22%20target%3D%22_blank%22%3E%40excelinLife%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(LEFT(A2%2C6)%2C%24F%242%3A%24G%2413%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20the%20formula%20in%20cell%20C2.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(NOT(ISNA(VLOOKUP(LEFT(B2%2C9)%2C%24J%242%3A%24K%2413%2C2%2CFALSE)))%2CAND(NUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B2%2CLEN(B2)-9))%26lt%3B%3D254))%2CVLOOKUP(LEFT(A2%2C6)%2C%24F%242%3A%24H%2413%2C3%2CFALSE)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThis%20is%20the%20formula%20in%20cell%20D2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20these%20formulas%20you%20can%20easily%20add%20more%20criteria.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3183174%22%20slang%3D%22en-US%22%3ERe%3A%20Location%20name%20if%20the%20cell%20begins%20with%20different%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183174%22%20slang%3D%22en-US%22%3EThank%20you%20could%20you%20please%20share%20the%20formula%20you%20shared%20yesterday%20without%20vlookup%20that%20suited%20the%20requirement%20perfectly%2C%20sorry.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3184076%22%20slang%3D%22en-US%22%3ERe%3A%20Location%20name%20if%20the%20cell%20begins%20with%20different%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3184076%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1310764%22%20target%3D%22_blank%22%3E%40excelinLife%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(LEFT(A4%2C6)%3D%22ABC1GB%22%2C%22UK%22%2CIF(LEFT(A4%2C6)%3D%22ABC1US%22%2C%22USA%22%2CIF(LEFT(A4%2C6)%3D%22ABC1AU%22%2C%22Australia%22%2CIF(LEFT(A4%2C6)%3D%22ABC1IN%22%2C%22India%22%2C%22%22))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20the%20formula%20in%20cell%20C2.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(LEFT(B4%2C9)%3D%2210.224.5.%22%2CAND(NUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26lt%3B%3D254))%2C%22London%22%2CIF(AND(LEFT(B4%2C9)%3D%2210.227.8.%22%2CAND(NUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26lt%3B%3D254))%2C%22Washington%22%2CIF(AND(LEFT(B4%2C9)%3D%2210.226.5.%22%2CAND(NUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26lt%3B%3D254))%2C%22Canberra%22%2CIF(AND(LEFT(B4%2C9)%3D%2210.221.4.%22%2CAND(NUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26gt%3B%3D1%2CNUMBERVALUE(RIGHT(B4%2CLEN(B4)-9))%26lt%3B%3D254))%2C%22New-Dehli%22%2C%22%22))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThis%20is%20the%20formula%20in%20cell%20D2.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have a excel sheet with 50000 rows of global data. I want to populate the Country by checking Name column if it begins with ABC1GB then Country is UK, if begins with ABC1US then Country is America, if beings with ABC1AU then Country is Australia, if beings with ABC1IN then Country is India. I want to populate Location by checking if the IP is between 10.224.5.1 and 10.224.5.254 then Location is London and so on. Please suggest the best approach to achieve this.

NameIP AddressCountryLocation
ABC1GBM10.224.5.7  
ABC1USM10.227.8.9  
ABC1AUM10.226.5.4  
ABC1INM10.221.4.3  
3 Replies
best response confirmed by excelinLife (New Contributor)
Solution

@excelinLife 

=VLOOKUP(LEFT(A2,6),$F$2:$G$13,2,FALSE)

This is the formula in cell C2.

=IF(AND(NOT(ISNA(VLOOKUP(LEFT(B2,9),$J$2:$K$13,2,FALSE))),AND(NUMBERVALUE(RIGHT(B2,LEN(B2)-9))>=1,NUMBERVALUE(RIGHT(B2,LEN(B2)-9))<=254)),VLOOKUP(LEFT(A2,6),$F$2:$H$13,3,FALSE),"")

 This is the formula in cell D2.

 

With these formulas you can easily add more criteria.

Thank you could you please share the formula you shared yesterday without vlookup that suited the requirement perfectly, sorry.

@excelinLife 

=IF(LEFT(A4,6)="ABC1GB","UK",IF(LEFT(A4,6)="ABC1US","USA",IF(LEFT(A4,6)="ABC1AU","Australia",IF(LEFT(A4,6)="ABC1IN","India",""))))

This is the formula in cell C2.

=IF(AND(LEFT(B4,9)="10.224.5.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"London",IF(AND(LEFT(B4,9)="10.227.8.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"Washington",IF(AND(LEFT(B4,9)="10.226.5.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"Canberra",IF(AND(LEFT(B4,9)="10.221.4.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"New-Dehli",""))))

 This is the formula in cell D2.