Extract Text or Number from a Column, Help.

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
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,899
Office Version
365, 2010
Platform
Windows
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

New Member
Joined
Aug 28, 2009
Messages
18
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-known Member
Joined
Apr 4, 2007
Messages
6,934
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

New Member
Joined
Aug 28, 2009
Messages
18
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
 

esuerez

New Member
Joined
Mar 2, 2016
Messages
2
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
 

Forum statistics

Threads
1,085,307
Messages
5,382,843
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top