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

[thebankerguy]

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 :

<TABLE style="WIDTH: 250pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=333 border=0 x:str><COLGROUP><COL style="WIDTH: 250pt; mso-width-source: userset; mso-width-alt: 12178" width=333><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699 0.5pt solid; BORDER-LEFT: #666699 0.5pt solid; WIDTH: 250pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=333 height=17>311615--Poultry Processing</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 0.5pt solid; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>812210--Funeral Homes and Funeral Services</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 0.5pt solid; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>421730--Warm Air Heating and Air-Conditioning Equipment and Suppplies Wholesalers</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 0.5pt solid; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>311222--Soybean Processing</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 0.5pt solid; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>325314--Fertilizer (Mixing Only) Manufacturing</TD></TR></TBODY></TABLE>

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!!

 

[Michael M]

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

=RIGHT(A1,LEN(A1)-FIND("--",A1)-1)

 

[thebankerguy]

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.

 

[thebankerguy]

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.

 

[GTO]

Hi there,

If you are just doing this once, you could run a junk/temp macro against it. Select the cells and run:

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

 

[thebankerguy]

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!