How to get rid of commas between part numbers in a cell?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: How to get rid of commas between part numbers in a cell?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to get rid of commas between part numbers in a cell?

     
    I have a worksheet with several fields/cells of which one contains part numbers separated by commas (such as: 8084430-001,8084430-003,8084430-011,8084430-013). These part numbers are of different lengths and count.
    I need to place each part number in a cell by itself so that it can be found by a vlookup from another worksheet. Is there a formula or VBA code that can accomplish this? It would take hours to do this manually.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get rid of commas between part numbers in a cell?

    Quote Originally Posted by mqarep View Post
    I have a worksheet with several fields/cells of which one contains part numbers separated by commas (such as: 8084430-001,8084430-003,8084430-011,8084430-013). These part numbers are of different lengths and count.
    I need to place each part number in a cell by itself so that it can be found by a vlookup from another worksheet. Is there a formula or VBA code that can accomplish this? It would take hours to do this manually.
    Have a look at Data|Text to Columns...|Delimited|Next|Comma as the delimter etc.

    Could you then use HLOOKUP instead of VLOOKUP?

    If not then Copy the horizontal data and then Edit|Paste Special...|Transpose|OK
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Invercargill
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get rid of commas between part numbers in a cell?

      
    You can use Data > Text to Columns, the choose comma delimiter.
    If you wan the results in a single column you will then have a manual job to take the 3 or 4 columns and move the data around

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com