Pulling a Unique 6 Digit Numbers from Irregular Cell List

13M13

New Member
Joined
Oct 16, 2018
Messages
2
I'm not sure if the best solution to my problem would be VBA or the application of several formulas but that's where the expertise of you all come in!

I need to retrieve a six digit number from a single cell that is formatted irregularly. The number can appear at the beginning of the cell, at the end of the cell, or somewhere in the middle. It is not reliably prefaced by any sort of symbol and it's not always the only number that will appear in the cell. Right now it seems that Flash Fill actually pulls the quickest results that are easier to clean up than anything else I've tried. Is there a formula or series of formulas I could use to get to where I'm going? I'm also open to VBA I just don't know where to start here.

Thanks in advance for your help!

Sample Title

<tbody>
</tbody>
Sample ID

<tbody>
</tbody>
Final ID

<tbody>
</tbody>
ipso facto 444555

<tbody>
</tbody>
444555

<tbody>
</tbody>
00444555

<tbody>
</tbody>
facto 444555

<tbody>
</tbody>
444555

<tbody>
</tbody>
00444555

<tbody>
</tbody>
ipso 323444

<tbody>
</tbody>
323444

<tbody>
</tbody>
00323444

<tbody>
</tbody>
ip 444666 so

<tbody>
</tbody>
444666

<tbody>
</tbody>
00444666

<tbody>
</tbody>
facto: 999666

<tbody>
</tbody>
999666

<tbody>
</tbody>
00999666

<tbody>
</tbody>
ipso #900655

<tbody>
</tbody>
900655

<tbody>
</tbody>
00900655

<tbody>
</tbody>
555666 ipso facto 444

<tbody>
</tbody>
555666

<tbody>
</tbody>
00555666

<tbody>
</tbody>
00444555 facto ipso

<tbody>
</tbody>
444555

<tbody>
</tbody>
00444555

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
See if this UDF (user defined function) does what you want...
Code:
Function GetID(S As String) As String
  Dim X As Long
  For X = Len(S) - 5 To 1 Step -1
    If Mid(S, X, 6) Like "######" Then
      GetID = Format$(Mid(S, X, 6), "00000000")
      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 GetID just like it was a built-in Excel function. For example,

=GetID(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.
 
Last edited:
Upvote 0
See if this UDF (user defined function) does what you want...
Code:
Function GetID(S As String) As String
  Dim X As Long
  For X = Len(S) - 5 To 1 Step -1
    If Mid(S, X, 6) Like "######" Then
      GetID = Format$(Mid(S, X, 6), "00000000")
      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 GetID just like it was a built-in Excel function. For example,

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


Thank you Rick! This worked like a charm on 94% of the data set. The only one that didn't pick up right were cells that had two different 6 or 8 digit number strings in the same cell which I've already flagged in a separate step so it's pretty much perfect!
 
Upvote 0
Thank you Rick! This worked like a charm on 94% of the data set. The only one that didn't pick up right were cells that had two different 6 or 8 digit number strings in the same cell which I've already flagged in a separate step so it's pretty much perfect!
This function may help you... it will gather all of the 6-digit numbers in the cell delimited by a comma/space.
Code:
[table="width: 500"]
[tr]
	[td]Function GetID(S As String) As String
  Dim X As Long
  For X = Len(S) - 5 To 1 Step -1
    If Mid(S, X, 6) Like "######" And Not Mid(S, X, 7) Like "#######" Then GetID = Format$(Mid(S, X, 6), "00000000") & " " & GetID
  Next
  GetID = Replace(Trim(GetID), " ", ", ")
End Function[/td]
[/tr]
[/table]
Note: This code assumes the rightmost 6 digits of any number in the text is a valid ID number.
 
Last edited:
Upvote 0
The only one that didn't pick up right were cells that had two different 6 or 8 digit number strings in the same cell
Welcome to the MrExcel board!

You didn't specify what result you wanted in this circumstance?

Also, is it possible for a cell to contain a 7-digit number or a 9,10,11...-digit number, either on their own or in combination with other text and/or 6 or 8 digit numbers?

Could you give us another set of sample data and expected results that includes the various different circumstances that might occur in your data?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top