Extract numbers/letters from a line of text with a twist

[mattyj7183]

I've read a few posts trying to solve this problem, but I think mine may be a little different than the ones I have seen so far. And please, by all means, if this has already been answered please guide me.

Essentially I have strings of text about 5000+ rows long with names, account numbers and other identifying info, but the task is to extract just the account numbers. Examples below all in A1:

PPC, Inc. (12345678, Corporate)
Smith Capital (ABCD Mod Cons), Raymond (98765432, Trust)
Jones, Rich E. & Jane A Doe (87654321, Exect)
Doe Family Trust (Stock), Joe & Jayne (11-F222-77-8, Trust)
Smith, II, Joe(122112288, Indiv (Crawford))
Jones, II, Steve(1234567),
Jones, II 2012 Trust, Josh R. (11112222, Trust)


First I tried text to columns using the "(" as the first delimiter and then the "," as the second. This worked on a good number, but the 5th Smith example I showed resulted in "Crawford))" showing in C3 which is not ideal. In some cases like the 2nd example it put part the account number in C3.

I also tried the GetNumber UDF I've seen floating around but that (as the name suggests) only grabs numbers but as you can see the 4th example has letters and numbers. It also automatically concatenated the numbers removing the "-" between each number. Both results not ideal.

Let me know if you need any further info.

Thanks in advance!

 

[Rick Rothstein]

Do your account numbers always start with a digit as your examples show?

 

[mattyj7183]

Rick - yes they do, at least for now.

 

[Rick Rothstein]

Rick - yes they do, at least for now.

I am a little concerned by your "at least for now" statement because if your account number could ever start with a letter (I could work around a symbol like # for example), the following would no longer work. However, as long as the account number starts with a digit, this should work for you...

Function AcctNums(S As String)
  Dim X As Long, Parts() As String
  Parts = Split(Replace(S, ")", ","), "(")
  For X = 0 To UBound(Parts)
    If Parts(X) Like "#*,*" Then
      AcctNums = Split(Parts(X), ",")(0)
      Exit Function
    End If
  Next
End Function

 

[mattyj7183]

Thank you for your reply, but unfortunately I am not totally sure where to put this. Is this function a macro or a UDF?

 

[Rick Rothstein]

Thank you for your reply, but unfortunately I am not totally sure where to put this. Is this function a macro or a UDF?

It is a function that can be used as a UDF (which is the abbreviation for User Defined 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 AcctNums just like it was a built-in Excel function. For example,

=AcctNums(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.

 

[mattyj7183]

Rick - This works perfectly! Thank you so much for you help.