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

Thread: How do your extract (delete) random numbers from text in a single column

  1. #1
    New Member
    Join Date
    Apr 2011
    Location
    Portland, OR
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do your extract (delete) random numbers from text in a single column

    I am working with an Excel spreadsheet issued by the SBA (Small Business Administration) where they list performance of loans based on specific NAICS Codes. Unfortunately they list the NAICS Code number and the Name of that NAICS Occupation in the same column.

    Example: it will read :

    311615--Poultry Processing
    812210--Funeral Homes and Funeral Services
    421730--Warm Air Heating and Air-Conditioning Equipment and Suppplies Wholesalers
    311222--Soybean Processing
    325314--Fertilizer (Mixing Only) Manufacturing


    The numbers are essentially random; I want to remove the numbers from the column but leave the text (that is the occupation names) so I can alphabetize the list. I don't need to retain the numbers (I have saved them in a separate worksheet) I just want to replace them with 'nothing', that is, delete them entirely. I have tried any number of 'find' and 'replace' fomulas to no avail. I don't want to go in and erase the numbers from 2600 rows, Help!!

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,990
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How do your extract (delete) random numbers from text in a single column

    If they are always 6 numbers and a double dash
    place this in the next column and copy down
    Assuming of course that your data starts in A1

    Code:
    =RIGHT(A1,LEN(A1)-FIND("--",A1)-1)
    Last edited by Michael M; Apr 11th, 2011 at 02:12 AM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    Apr 2011
    Location
    Portland, OR
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: How do your extract (delete) random numbers from text in a single column

    Awesome! Thanks Michael, worked very well...just pushed the Col B over Col Ato hide Col A, as you can't obviously delete Col A, so you saved me about 2 hours of tedious work! You have good karma going the rest of the week, thanks again.

  4. #4
    New Member
    Join Date
    Apr 2011
    Location
    Portland, OR
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do your extract (delete) random numbers from text in a single column

    Also Michael, I was looking at the fomula, say the numbers were not all the same length, how could the formula be changed to accomodate that? If you have the time to respond to that. Thanks again.

  5. #5
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do your extract (delete) random numbers from text in a single column

    Hi there,

    If you are just doing this once, you could run a junk/temp macro against it. Select the cells and run:
    Code:
    Option Explicit
        
    Sub exa5()
    Dim Cell As Range
        For Each Cell In Selection
            If Not InStr(1, Cell.Text, "--") = 0 Then
                Cell.Value = Trim(Mid(Cell.Value, InStr(Cell.Text, "--") + 2))
            End If
        Next
    End Sub

  6. #6
    New Member
    Join Date
    Apr 2011
    Location
    Portland, OR
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do your extract (delete) random numbers from text in a single column

    Thanks, I will try that on some other data in a different spreadsheet of data where I have a similar issue...amazing how something so simple is much more difficult than I first imagined!

Some videos you may like

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
  •