1

Good day everyone,

I am trying to find a smart solution of extracting 8 digits from a cell (unique ID). The problem here occurs that it might look like this:

112, 65478411, sale
746, id65478411, sale 12.50
999, 65478411
999, id65478411

Thats most of the cases, and probably all mentioned, so I basically need to find the 8 digits in the cell and extract them into different cell. Does anyone have any ideas? I though of eliminating the first characted, then check if the cell is starting with the id, eliminate it further but I understood that this is not the smart way..

Thank you for the insights.

user3812753
  • 79
  • 2
  • 8
  • `instr` for ", " then do a `left` for 8 from there. – findwindow Dec 08 '15 at 22:34
  • Is it always the forth number, ie is there always a three digit number with a comma in the front? – Scott Craner Dec 08 '15 at 22:36
  • @ScottCraner there could be a 3 digit number with a comma or without a comma, depends on the people filling out the spreadsheet – user3812753 Dec 08 '15 at 22:39
  • @findwindow sorry, forgot to mention that not always a comma exist there. Maybe this helps that every number starts with 6 and then 7 digits? – user3812753 Dec 08 '15 at 22:39
  • oh lol didn't even see the `id` my fault XD Edit: don't debug and comment! – findwindow Dec 08 '15 at 22:41
  • 1
    "depends on the people filling out the spreadsheet" - shudder. That means they might do anything to the cell and no logic or rule can be applied. Restrict what they can enter. Let them enter the ID into a different cell and use data validation. – teylyn Dec 08 '15 at 22:43
  • @teylyn thanks for your advise, I am thinking about that for a while, problem is that there might be more than 20-30 workbooks which operates on the same logic... Corporate stuff :) – user3812753 Dec 08 '15 at 22:45
  • 2
    I suspect you could get it to work using Regex and some instructions described in this highly upvoted answer here using a match pattern something like `[0-9]{8,8}: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Marc Dec 08 '15 at 22:45
  • 1
    @pnuts works like a charm. I believe I will go with your solution as this I think is as simple as this could get. I will still check for macro based solution if any will occur. Thank you very much. – user3812753 Dec 08 '15 at 22:46
  • @marc thanks for the regex. I saw it few times but never came up to step by step instructions. Will definetely find it where to use. – user3812753 Dec 08 '15 at 22:49

3 Answers3

5

Try this formula:

=--TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0),"00000000")

This will return the 8 digit number in the string.

To return just the text then:

=TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0),"00000000")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    @pnuts when I was building it I wanted to insure that any leading zeroes were captured, then thought I thought I needed to change it back to a number. I kept adding till it worked. Then took it too far. :) – Scott Craner Dec 09 '15 at 01:48
1

You can also write a UDF to accomplish this task, example below

Public Function GetMy8Digits(cell As Range)
Dim s As String
Dim i As Integer
Dim answer
Dim counter As Integer

'get cell value
s = cell.Value

'set the counter
counter = 0
'loop through the entire string
For i = 1 To Len(s)
    'check to see if the character is a numeric one
    If IsNumeric(Mid(s, i, 1)) = True Then
        'add it to the answer
        answer = answer + Mid(s, i, 1)
        counter = counter + 1
        'check to see if we have reached 8 digits
        If counter = 8 Then
            GetMy8Digits = answer
            Exit Function
        End If
     Else
     'was not numeric so reset counter and answer
     counter = 0
     answer = ""
    End If
Next i
End Function
Sorceri
  • 7,870
  • 1
  • 29
  • 38
  • I am not sure where one post did go, but I have used that function that was called "extracteight".... Thanks though! – user3812753 Dec 08 '15 at 23:11
  • 1
    @user3812753 ok, great that you are using the other solution but I want to point out, if my memory serves me correct, that the other solution was checking 8 character chunks for IsNumeric so 6547.8411 would return true. The reason I did the approach I did was that it would fail if it came across a period. therefor 6547.8411 would not be returned as your 8 digits. - I am aware that 6547.8411 is 9 digits but I wanted to show the full number so as to alleviate anyone coming back stating the number was not correct.....hope that makes sense – Sorceri Dec 09 '15 at 15:15
0

Here is an alternative:

=RIGHT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A4),LEN(A1))),8)

Replace all commas with spaces repeated the length of the string,

Then take the mid point starting from the length of the original string for the length of the string (ie second word in new string)

Trim out the spaces

take the right 8 chars to trim out any extra chars (like id)

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36