Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing text to a specified number (2007)

    I have a spreadsheet for a league schedule with columns A & B containing a variety of team names, some of which are repeated numerous times. I have a separate chart that has a list of the teams with a number assigned to each team. I need to convert all of the team names in columns A & B to the appropriate numbers. Is this best done by a macro? What would the code look like? How do I tell it to take the data from the other chart and use it? What if other teams are added to that chart later? Is it better to have the data from that chart in the same spreadsheet or a different one, or does it matter? (And I need to do a similar function to change site names from another column to a numeric code as well.)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Changing text to a specified number (2007)

    You could add two new columns with a lookup formula to the table with the teams and numbers in. (it's not clear to me why you want to do this though?) When you say 'chart' do you actually mean chart or do you mean table? It doesn't really matter if the data is in the same workbook or in a different one, but if it's only used for this workbook, you may as well keep it all in one location.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing text to a specified number (2007)

    Thanks, Rory. I said "chart" because that is only data from a website at present. So, if I enter the info from that chart into columns Y & Z, such that column Y contains all of the team names and column Z contains the corresponding numeric designation, how do I use a lookup formula to change all of the entries in Columns A & B to the numeric designation? And where does that formula reside, how is it used? (Sorry to be so ignorant; this is new to me.)
    Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing text to a specified number (2007)

    See the attached workbook for an example of looking up the team codes using the VLOOKUP function.
    It you want to replace the team names with the corresponding codes, you can do the following:
    - Select the range with the formulas (C213 in the sample worksheet).
    - Select Edit | Copy.
    - Select Edit | Paste Special...
    - Click the 'Values' option and click OK. This replaces the formulas with their result.
    - You can now delete the columns with the team names.

    (The word 'chart' is generally used in Excel as a synonym for 'graph', such as a bar chart, line chart or pie chart. Hence the confusion)
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing text to a specified number (2007)

    Thanks, Hans...I'm still struggling. Can't get it to work right. I get the #N/A error. I am attaching a stripped down version if someone is able to figure out what I am doing wrong.

    Thanks.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing text to a specified number (2007)

    There are one or more spaces after the team names in columns A and B. This makes the VLOOKUP formulas fail.

    You can use the macro from <post:=668,819>post 668,819</post:> to remove leading and trailing spaces from text values.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing text to a specified number (2007)

    Thanks once again. That solved all of the troubles (for now). I appreciate your expertise and helpfulness beyond words.

Posting Permissions

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