Thread: pick the number from a group

dear all,
my cell a1 = SOME 3 DIGIT numbers, b1 = 5678 my quistion is this how to pick the 5678 touching numbers in c1

Could you edit the sample workbook, to show what the final results should be.

Save it as a .xls, it would be easier for people without 'xl07 to down load

Here's a UserDefinedFunction (UDF) you can add to your sheet to give the capability to do this:

Code:

Function StringTest(RNG As Range, RNG2 As Range)
'JBeaucaire  (12/14/2009)
Dim MyArr, MyArr2, buf As String, Chk  As Boolean
Dim i As Long, j As Long, Cnt As Long

MyArr = Split(Application.WorksheetFunction.Substitute(RNG.Value, Chr(10), "-"), "-")
MyArr2 = Split(RNG2.Value, "-")

For i = 0 To UBound(MyArr)
    For j = 0 To UBound(MyArr2)
        If InStr(MyArr(i), MyArr2(j)) Then Chk = True
    Next j
    If Chk Then
        buf = buf & MyArr(i) & "-"
        Cnt = Cnt + 4
    End If
    If Cnt = 40 Then
        buf = Left(buf, Len(buf) - 1) & Chr(10)
        Cnt = 0
    End If
    Chk = False
Next i

StringTest = buf

End Function

=========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet as a macro-enabled workbook (.xlsm)

The function is installed and ready to use.
=============

Now, based on your sample sheet, you enter this formula in C2:

=STRINGTEST(A2, B2)

sun_ilkumar,

please use the forum facilites to attach files. Linking to files on non-trusted external web sites is not the preferred option.

Your sample sheet does not match your sheet in post #1 at all. I've designed the UDF to fit your requirements from the posted sheet.

Here is your original sheet in it with the UDF already installed.

For what reason would you want to do such a thing?

Anyway check out
=CONCATENATE()

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].



(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)