Pulling a Unique 6 Digit Numbers from Irregular Cell List

1

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!

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]Sample Title[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Sample ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]Final ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]ipso facto 444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]facto 444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]ipso 323444[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]323444[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00323444[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]ip 444666 so[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]444666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00444666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]facto: 999666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]999666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00999666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]ipso #900655[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]900655[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00900655[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]555666 ipso facto 444[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]555666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00555666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138"]00444555 facto ipso[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl66, width: 74"]00444555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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
Rick Rothstein said:
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.
Click to expand...


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
13M13 said:
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!
Click to expand...
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
13M13 said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Help! pull specific text from cell
Replies
4
Views
742
jtakw
jtakw
J
  • Question Question
Formula for pulling specific information from a cell with too much and varying characters
Replies
2
Views
524
Sufiyan97
Sufiyan97
J
  • Question Question
Moving from VBA to a formula
Replies
1
Views
673
jerH
J
I
  • Question Question
Pulling a specific text/number from a cell
Replies
3
Views
652
Sektor
Sektor
A
  • Question Question
Pulling Data From an Unknown Number of Cells
Replies
1
Views
296
Dryver14
D

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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