Extracting Account number from lines. Accounts are in different locations on the line.

[miamiman33176]

I am hoping that someone can assist me. I have lines of data that contains account number that are all 10 numerical charcters. The account numbers only show up one time in the cell and are in different locations in the cell on different lines. Is there a formula that I can use to extract a 10 digit number from anywhere in the time? There are some numbers that are more that 10 and less than ten, but I only care about the ten digit numbers that are grouped together.

Thanks.

 

[JoeMo]

Your post is not clear to me. You might improve your chances of getting some help by posting some sample data and expected results in a format that can be copied from a browser and pasted into Excel.

 

[Fennek]

Hello,

one possible way is with RegEx and the .Pattern "\d{10}"

regards

 

[Nishant94]

Maybe this:

Unknown
	A	B
1	Text	Account No.
2	No. 1111223423 44334 Account	1111223423
3	5896 2547823423 Kapil No.	2547823423
4	Example Sample 1589657456 Do	1589657456
Sheet4

Cell Formulas
Range	Formula
B2	{=RIGHT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1,LEN(A2)*MATCH(10,LEN(SUBSTITUTE(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+(LEN(A2)-1)*(ROW(INDIRECT("1:"&(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))-1),LEN(A2))," ","")),0))),10)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[István Hirsch]

Maybe this:

Unknown

	A	B
1	Text	Account No.
2	No. 1111223423 44334 Account	1111223423
3	5896 2547823423 Kapil No.	2547823423
4	Example Sample 1589657456 Do	1589657456

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula B2 {=RIGHT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1,LEN(A2)*MATCH(10,LEN(SUBSTITUTE(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+(LEN(A2)-1)*(ROW(INDIRECT("1:"&(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))-1),LEN(A2))," ","")),0))),10)} <thead> </thead><tbody> </tbody> Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Issue: if an eleven-digit number precedes the ten-digit account number, the former will be extracted.

 

[Rick Rothstein]

Can you make use of a VBA solution? If so, here is a UDF (user defined function) that will reliably return the (first) 10-digit stand-alone number in the text... if there is no 10-digit number in the text, it will return the empty text string (""). Note that the function returns the number as Text so that if the number starts with one or more leading zeros, those zeros are preserved.

Function TenDigits(ByVal S As String) As String
  Dim X As Long
  S = " " & S & " "
  For X = 1 To Len(S) + 2
    If Mid(S, X, 12) Like "[!0-9]##########[!0-9]" Then
      TenDigits = Mid(S, X + 1, 10)
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TenDigits just like it was a built-in Excel function. For example,

=TenDigits(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[István Hirsch]

Try this UDF:

Function GetAcc(s As String) As String
    With CreateObject("VBScript.Regexp")
        .Pattern = "\b[\d]{10}\b"
        If .test(s) Then GetAcc = .Execute(s)(0)
    End With
End Function