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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.