Extract Text or Number from a Column, Help.

M

MaxExcel

New Member
Joined
Aug 28, 2009
Messages
18
Hi All,

If anyone have a solution for below mentioned problem.

I've have thousands of lines of data such as follow: (in column A)
________________________________________________________________
<TABLE style="WIDTH: 406pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=541 border=0 x:str><COLGROUP><COL style="WIDTH: 406pt; mso-width-source: userset; mso-width-alt: 19785" width=541><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 406pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=541 height=17 x:str="ACTION ACCTOUNT TITLE ACCOUNT# ">ACTION ACCTOUNT TITLE ACCOUNT#

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTION TRUST U/A BOYCE 123456789000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTION IRA GEORGE 123456789456 / 12345678045</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTION IRA GEORGE W J 12345678901</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTION TRUST U/A BOYCE D DAVIS W 123456789000 / 123456789000</TD></TR></TBODY></TABLE>
_________________________________________________________________

and i want to separate the text from the number so the result as follow:

(Column B)

<TABLE style="WIDTH: 216pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=288 border=0 x:str><COLGROUP><COL style="WIDTH: 216pt; mso-width-source: userset; mso-width-alt: 10532" width=288><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 216pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=288 height=17 x:str="ACTION TRUST U/A BOYCE ">ACTION TRUST U/A BOYCE </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTION IRA GEORGE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTION IRA GEORGE W J</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="ACTION TRUST U/A BOYCE D DAVIS W ">ACTION TRUST U/A BOYCE D DAVIS W </TD></TR></TBODY></TABLE>

(Column C)

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>123456789000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>123456789456 /12345678045</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12345678901</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>123456789000 /123456789000</TD></TR></TBODY></TABLE>

Thank you for all your attention!

Kindly
MaxExcel
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
I don't assume the number part always starts with 1? I just assume that is how this sample data is? If it does always start with 1 it can be done pretty easily, if not we would need to see some more samples, or atleast I will. :)
 
Upvote 0
In B1:
=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

In C1:
=TRIM(REPLACE(A1,1,LEN(B1),""))

Copy down.
 
Upvote 0
Thank you so much for your quick respond and clarification. The account#s are do not always start with "1" and it can be any number.

Kindly,
MaxExcel
 
Upvote 0
Well All be. I should have thought of that. Thanks for that HotPepper, because that is going in my favorites folder on here. That should do it. :)
 
Upvote 0
Thank you for all your helpful feedback! Just to expand the question to cover more cases.

What would you do when you have

1) Number, Text, Number, Text, Number

303983 John Smith 123456789000 News Corp 9748347
303983 John Smith and Linda SmithO 123456789000 News Corp 9748347

2)Number, Text, Number

71 Bisant Maker V/A T IRA 56413156454/78979765
012 Altoids J. Deare jr LLP 123135498795/1554564

Kindly
MaxExcel
 
Upvote 0
Here's a quick little vba example that should work for your first and latest example data:
Code: 
Sub GoNumeric()
Dim Rng As Range, c As Range, NumChrs As String
Set Rng = Range("A1:A4")
For Each c In Rng
  NumChrs = ""
  For i = 1 To Len(c)
    If IsNumeric(Mid(c, i, 1)) Then NumChrs = NumChrs & Mid(c, i, 1)
  Next i
  c = NumChrs
Next c
End Sub
Note, it assumes your range if interest is A1:A4 - change that to suit.
It also assumes you want all numeric characters entered as one long number (no spaces) to replace the original data in A1:A4 (not recommended but this is just an example).

Hope it helps.
 
Upvote 0
Enjoy it

Code: 
Function GoNumeric1(target As Range) As Double    
    Set Rng = target
    
    For I = 1 To Len(Rng)
      letter = Mid(Rng, I, 1) 'each character
      If Asc(letter) > 47 And Asc(letter) < 58 Or Asc(letter) = 46 Then
        finishedString = finishedString + letter
      End If
    Next I
    
    If finishedString <> "" Then GoNumeric1 = finishedString


End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
642
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
M
  • Question Question
TEXTAFTER with textbefore...to extract number or series of numbers.
2
Replies
12
Views
1K
mad3
M
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
928
epactheactor
E

Forum statistics

Threads
1,224,808
Messages
6,181,073
Members
453,020
Latest member
mattg2448

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