+ Reply to Thread
Results 1 to 14 of 14

Translating City Codes to Names (example attached)

  1. #1
    Forum Contributor
    Join Date
    09-02-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    110

    Translating City Codes to Names (example attached)

    I have a large database from a central appraisal district. In the database the city is stored as a numerical value. The problem is I need to convert the numerical value for the city to a text string with the actual city name. For example the values to the attached example database are as follow:
    excel help forum 2.xlsx
    024 = Hurst
    026 = Fort Worth
    025 = Euless
    017 = Mansfield
    013 = Keller

    The issue is, I need to convert this column of numbers into the string of the actual city name. Is there some type of command, or macro that I could use to automate this process? Attached is a small example copy of the database.

    Thank you
    Last edited by cmf0106; 01-30-2014 at 12:33 PM.

  2. #2
    Registered User
    Join Date
    11-26-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Translating City Codes to Names (example attached)

    Hi

    You can probably use the vlookup-function if you have the list of city-codes stored somewhere.
    Best regards

    Per Erik Midtr?d

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,640

    Re: Translating City Codes to Names (example attached)

    Your request is very vague and at least for me the attachment isn't much help. Mostly you can use (as perimidt noted) a vlookup function to accomplish this. But your request also seems to indicate you the numerical value to a text string? It might help if you altered your attachment to show what you have and your desired results.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Contributor
    Join Date
    09-02-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Translating City Codes to Names (example attached)

    This new excel file should clarify what I am attempting to do with vlookup. The vlook up table values are on the 2nd sheet of the excel file.
    vlookup.xlsx

  5. #5
    Registered User
    Join Date
    11-26-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Translating City Codes to Names (example attached)

    The following in D2 should work:
    =VLOOKUP(C2*1,Vlookuptable!A:B,2,FALSE)

  6. #6
    Forum Contributor
    Join Date
    09-02-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Translating City Codes to Names (example attached)

    Quote Originally Posted by perimidt View Post
    The following in D2 should work:
    =VLOOKUP(C2*1,Vlookuptable!A:B,2,FALSE)
    Thank you for your help!

  7. #7
    Forum Contributor
    Join Date
    09-02-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Translating City Codes to Names (example attached)

    Quote Originally Posted by perimidt View Post
    The following in D2 should work:
    =VLOOKUP(C2*1,Vlookuptable!A:B,2,FALSE)
    Wanted to ask as well, why is the *1 needed on C2 for the city column? Thanks!

  8. #8
    Registered User
    Join Date
    11-26-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Translating City Codes to Names (example attached)

    Hi

    That is because the numbers on the Data-sheet is actually stored as text and the numbers on the vlookuptable-sheet are stored as numbers.
    When you multiply a number stored as text it gets converted to a real number.

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,640

    Re: Translating City Codes to Names (example attached)

    Try it alone in cell E2, just =c2*1 to see what it gives you.

  10. #10
    Forum Contributor
    Join Date
    09-02-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Translating City Codes to Names (example attached)

    Quote Originally Posted by perimidt View Post
    Hi

    That is because the numbers on the Data-sheet is actually stored as text and the numbers on the vlookuptable-sheet are stored as numbers.
    When you multiply a number stored as text it gets converted to a real number.
    Thanks again. I tried to run the same vlookup function on the real data set; however, it keeps returning n/a. I am not sure what I am doing incorrectly if you wouldn't mind quickly taking a look.
    example3.xlsx

    Thank you so much.

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,640

    Re: Translating City Codes to Names (example attached)

    you don't have the d2*1 in the formula.

  12. #12
    Forum Contributor
    Join Date
    09-02-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Translating City Codes to Names (example attached)

    Quote Originally Posted by Sambo kid View Post
    you don't have the d2*1 in the formula.
    I removed it because they are now both stored as numerical data. Just to experiment adding the d2*1 does not correct it.

  13. #13
    Registered User
    Join Date
    11-26-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Translating City Codes to Names (example attached)

    You have renamed the sheet from vlookup to VB_lookup without changing the formula.

  14. #14
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,640

    Re: Translating City Codes to Names (example attached)

    When I did it, it worked, but got rid of your table reference.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro codes for sending the one sheet attached through outlook
    By Ram Behera in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2013, 10:42 AM
  2. Macro codes for sending the one sheet attached through outlook
    By Ram Behera in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-27-2013, 10:42 AM
  3. Checking names in a list, then updating corresponding data [see attached file]
    By Ken Knee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2011, 09:42 AM
  4. Replies: 3
    Last Post: 12-12-2006, 07:27 PM
  5. Translating function names
    By dziw in forum Excel General
    Replies: 2
    Last Post: 06-29-2005, 11:37 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1