+ Reply to Thread
Results 1 to 10 of 10

Extract Country Code from raw data

  1. 04-02-2015, 09:58 PM #1
    sabubakaralis
    sabubakaralis is offline
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Extract Country Code from raw data

    Hi Everyone,

    Please see my attachment and help me to solve this. the question is here:The Country code is between the first & second Underscore character ("_") i.e. the formula has to first check for the existence of the 1st & 2nd "_" character and then extract the Country code otherwise write the County value as Other.


    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-02-2015, 10:01 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Extract Country Code from raw data

    soooooo the country code with this (B127)...
    Test_Rachel_Smith_Advertiser

    would be Rachael?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 04-02-2015, 10:14 PM #3
    sabubakaralis
    sabubakaralis is offline
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Re: Extract Country Code from raw data

    No
    That's look like name.
    Country Code is like this: US/UK/IE/AE.
    And this (Test_Rachel_Smith_Advertiser) must appear "Other"
    Whatsoever other than country code should appear "Other"
    Reply With QuoteRegister To Reply
  4. 04-02-2015, 10:18 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Extract Country Code from raw data

    so...
    EMEA_PL_IKEA

    would be PL?

    do you have a list of codes?
    Are they always 2 characters?
    Reply With QuoteRegister To Reply
  5. 04-02-2015, 10:26 PM #5
    sabubakaralis
    sabubakaralis is offline
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Re: Extract Country Code from raw data

    Yes ,

    you are right.
    Reply With QuoteRegister To Reply
  6. 04-02-2015, 10:27 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Extract Country Code from raw data

    and my other 2 questions?
    Reply With QuoteRegister To Reply
  7. 04-02-2015, 10:41 PM #7
    sabubakaralis
    sabubakaralis is offline
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Re: Extract Country Code from raw data

    Please see the attachment of country codes.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-02-2015, 10:42 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Extract Country Code from raw data

    Perhaps this...
    A
    B
    C
    1
    		 Country Code Advertiser US
    2
    		 US NA_US_Procter & Gamble (Procter & Gamble) UK
    3
    		 US NA_US_Procter & Gamble (Procter & Gamble) IE
    4
    		 US NA_US_Procter & Gamble (Procter & Gamble) AE
    5
    		 US NA_US_Procter & Gamble (Procter & Gamble)
    6
    		 US NA_US_Procter & Gamble (Procter & Gamble)
    7
    		 US NA_US_Procter & Gamble (Procter & Gamble)
    8
    		 US NA_US_Procter & Gamble (Procter & Gamble)
    9
    		 US NA_US_Procter & Gamble (Procter & Gamble)
    10
    		 US NA_US_Procter & Gamble (Procter & Gamble)
    11
    		 Youtube Test Advertiser - East Youtube Test Advertiser - East
    12
    		 Youtube Test Advertiser - East Youtube Test Advertiser - East

    A2=IFERROR(VLOOKUP(LEFT(MID(B2,FIND("_",B2,1)+1,99),2),$C$1:$C$4,1,0),B2)
    C1:C4 is the range of codes - extend if necessary
    Reply With QuoteRegister To Reply
  9. 04-02-2015, 11:13 PM #9
    sabubakaralis
    sabubakaralis is offline
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Re: Extract Country Code from raw data

    Thank you.

    For you prompt response and kind efforts.
    Reply With QuoteRegister To Reply
  10. 04-03-2015, 12:10 AM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Extract Country Code from raw data

    Based on the 2nd file, I added that data to your 1st file, in columns C and D. Your formula then becomes...
    =IFERROR(VLOOKUP(LEFT(MID(B2,FIND("_",B2,1)+1,99),2),$C$1:$C$47,1,0),B2)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. extract data based on four criteria in excel sheet or VBA code to extract
    By navanee in forum Excel General
    Replies: 3
    Last Post: 12-18-2014, 10:27 AM
  2. Extract a country name from the different String
    By amriali in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2013, 06:25 AM
  3. how to automatically extract country name from address in excell 2007
    By puissmisteri in forum Excel General
    Replies: 12
    Last Post: 12-30-2011, 12:52 AM
  4. how to automatically extract country name from address in excell ?
    By puissmisteri in forum Excel General
    Replies: 1
    Last Post: 12-22-2011, 09:57 AM
  5. Excel 2007 : how to automatically extract country name from address in excell 2007
    By puissmisteri in forum Excel General
    Replies: 1
    Last Post: 12-22-2011, 09:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1