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

T

thebankerguy

New Member
Joined
Apr 7, 2011
Messages
4
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!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Hi there,

If you are just doing this once, you could run a junk/temp macro against it. Select the cells and run:
Rich (BB 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
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
816
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
M
  • Question
TEXTAFTER with textbefore...to extract number or series of numbers.
2
Replies
12
Views
391
mad3
M
E
  • Question
VBA for Web Scraping help
Replies
0
Views
581
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,606
Messages
6,120,479
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top