excel - parsing out information in rows

i have about 1000 rows that have log information.

here is an example of one of the rows
"<Bill generation for>: Account 0278069001 not billed – No unbilled meter readings were found for service WW IR"


I am interested in the accountnumber information in the row that is ten digits.   I need to extract that out of the rows and put it in a separate column beside the cell.
example.  
"<Bill generation for>: Account 0278069001 not billed – No unbilled meter readings were found for service WW IR"

would have in cell a1
 "<Bill generation for>: Account 0278069001 not billed – No unbilled meter readings were found for service WW IR"
and in a2 it would have   (this needs to be extracted out of a1)
 "0278069001"


the account numbers vary in positional placement in the row.  can't use text to data or fixed with.
I need a formula that parses the content of the cell (a1) for a number of 10 digits in length and places it (a2)
jamesmetcalf74Asked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
To only find numbers 10 digits in length, use the pattern \d{10}...

Function GetNumber(rng As Range) As String
    Dim RE As Object
    Set RE = CreateObject("VBScript.RegExp")
    With RE
       .Global = True
       .Pattern = "\d{10}"
    End With
    If RE.Test(rng.Value) Then
       GetNumber = RE.Execute(rng.Value).Item(0).Value
    End If
End Function

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try this User Defined Function...

Function GetNumber(rng As Range) As String
Dim Num As String
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
   .Global = True
   .Pattern = "\d"
End With
If RE.Test(rng.Value) Then
   Set Matches = RE.Execute(rng.Value)
   For Each Match In Matches
      Num = Num & Match.Value
   Next Match
End If
GetNumber = Num
End Function

Open in new window



And then use it on Sheet like this...
Assuming your string is in A1, then

In A2
=GetNumber(A1)

Open in new window

GetNumberFromString.xlsm
0
 
SteveCommented:
If your account numbers are always 10 digits long then the following formula should work:

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),10)

Open in new window

( change A1 depending on layout of sheet , you may need to Press [Ctrl]+[shift]+[enter] when using it )
0
 
Rob HensonFinance AnalystCommented:
If the string will always have the word Account in it you can use this formula:

=MID(A1,FIND("Account",A1,1)+8,10)

Thanks
Rob
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The chosen answers resolved the question. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.