Extracting Digits.

[serge]

Hi everyone,


I'm looking for a formula that will extract, all the digits used in a series of 5 number, minus 1,2,3.
See example below.

Thank you.

Excel Workbook
	D	E	F	G	H	I	J
5	8	27	32	35	38		5788
6	1	9	10	23	28		089
7	5	20	27	27	30		00577
Sheet

 

[alansidman]

Please explain in simple English your logic using your example to arrive at your answer. It is not obvious to me.

 

[serge]

Thanks Alan to respond to my post.
For example let take the first row : 8 27 32 35 38 Digits are : 2 2 3 3 3 5 7 8 8 but I don't want to keep the digits 1,2 or 3 so it left me with : 5 7 8 8.

 

[lrobbo314]

This UDF seems to do what you are looking for.

Function ExtractNumbers(r As Range)
Dim d As Object
Dim s() As String
Dim res As String
Dim tmp As String
Dim c As String
Dim cel As Range


Set d = CreateObject("Scripting.Dictionary")


For Each cel In r
    For i = 1 To Len(cel.Value)
        c = Mid(cel.Value, i, 1)
        If c > 3 Or c = 0 Then
            res = res + c & "-"
        End If
    Next i
Next cel


res = Left(res, Len(res) - 1)
s = Split(res, "-")


For i = 0 To UBound(s)
    For j = i To UBound(s)
        If s(i) > s(j) Then
            tmp = s(i)
            s(i) = s(j)
            s(j) = tmp
        End If
    Next j
Next i


ExtractNumbers = Join(s, "")


End Function

Then your formula would look like =ExtractNumbers(B2:F2)

 

[serge]

Thank you Irobbo, but I prefer a formula if possible, I'm not good with UDF, thank you for the help.

 

[serge]

Does anyone has an idea about a formula ?
Thank you.

 

[Rick Rothstein]

Does anyone has an idea about a formula ?

I think you will have trouble finding a formula solution for what you are trying to do. If one exists, I am sure it will involve some helper columns. Anyway, you should give a UDF solution a try... they are easier to implement than you might be imagining. I'll give you instructions below in case you want to give it a try. First though, even if you don't want to try it, I thought lrobbo314 might find the UDF that I developed to be of interest...

Function ExtractDigits(R As Range) As String
  Dim X As Long, Digits As Variant
  Digits = Split(StrConv(Replace(Replace(Replace(Join(Application.Index(R.Value, 1, 0), ""), 1, ""), 2, ""), 3, ""), vbUnicode), Chr(0))
  With CreateObject("System.Collections.ArrayList")
    For X = 0 To UBound(Digits) - 1
      .Add CStr(Digits(X))
    Next
    .Sort
    ExtractDigits = Join(.ToArray, "")
  End With
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 NameOfTheUDF just like it was a built-in Excel function. For example, assuming your numbers start in Row 2 and are located in Columns A thru E, put this formula your output cell (cell F2 would be my guess) and copy it down as needed...

=ExtractDigits(A2:E2)

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.

 

[serge]

Then, I don't have a choice, you guys knows better, so I will use your UDF s, because I really need it.
Thank you, Irobbo314 and Rick for your precious help, I really appreciate it.

 

[lrobbo314]

I thought lrobbo314 might find the UDF that I developed to be of interest...

Function ExtractDigits(R As Range) As String
  Dim X As Long, Digits As Variant
  Digits = Split(StrConv(Replace(Replace(Replace(Join(Application.Index(R.Value, 1, 0), ""), 1, ""), 2, ""), 3, ""), vbUnicode), Chr(0))
  With CreateObject("System.Collections.ArrayList")
    For X = 0 To UBound(Digits) - 1
      .Add CStr(Digits(X))
    Next
    .Sort
    ExtractDigits = Join(.ToArray, "")
  End With
End Function

Awesome Rick. And for once, I totally get everything you wrote in the code. And craziest of all... I've been coding all this time and I never had any idea that ArrayLists were available in VBA. What a revelation! So cool.

 

[Ron Coderre]

I know this is a bit late....but I couldn't resist the challenge to solve your dilema without VBA.
Using your sample values in D5:H7

This ARRAY-FORMULA (completed by holding down CTRL and SHIFT when you press ENTER, instead of just pressing ENTER)
lists the digits greater than 3, in ascending order, from the referenced cell:

J5: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUM(
SMALL(--MID(D5&E5&F5&G5&H5,ROW(INDIRECT("1:"&LEN(D5&E5&F5&G5&H5))),1),
ROW(INDIRECT("1:"&LEN(D5&E5&F5&G5&H5))))*
10^(LEN(D5&E5&F5&G5&H5)-ROW(INDIRECT("1:"&LEN(D5&E5&F5&G5&H5)))))
,0,""),1,""),2,""),3,"")

Copy K5 and paste into K6 and down as far as you need.
(Obviously, if helper cells were used, the solution would be easier to understand)

Is that something you can work with?