Extract Text or Number from a Column, Help.

[MaxExcel]

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

 

[schielrn]

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.

 

[Scott Huish]

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.

 

[MaxExcel]

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

 

[schielrn]

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.

 

[MaxExcel]

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

 

[HalfAce]

Here's a quick little vba example that should work for your first and latest example data:

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.

 

[esuerez]

Enjoy it

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