Extracting the first 6 numbers from a text string (that has other numbers)

simplethinks · Oct 15th 2013

[TABLE="width: 406"]

[tr]

[td]

I have a list of strings like the ones below.

el_Malcolm_220255_eded41b7ea3932b3f5f089c97d91db5e.arb

[/td]

[/tr]

[tr]

[td]

el_Melissa_220310_92d21f1a34be9d21f92ec234b69b99be.arb

[/td]

[/tr]

[tr]

[td]

el_George Benson_276938_Request_1_of_1.html

[/td]

[/tr]

[tr]

[td]

el_Africa_276959_Request_1_of_1.html

[/td]

[/tr]

[tr]

[td]

el_European Parliament Lists_276948_Request_1_of_1.html

[/td]

[/tr]

[tr]

[td]

el_Aeroplane_276944_Request_1_of_1.html

[/td]

[/tr]

[tr]

[td]

el_Movies_293264_ANSWER_en_20130911-58123.xml

[/td]

[/tr]

[tr]

[td]

el_Feldman_220430_(2).txt

[/td]

[/tr]

[/TABLE]

I have tried several formulas but most of them extract the 6 numbers but fail to do so if the string has other numbers after the ones extracted. (see strings marked in red) Can anyone help?

Thanks,
Simplethinks

cytop · Oct 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Try using text to columns with the _ as the seperator....

simplethinks · Oct 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Thanks cytop.

I only want the first 6 numbers. I want to extract the first 6 numbers of every string in a new column.
e.g.
el_Malcolm_220255_eded41b7ea3932b3f5f089c97d91db5e.arb | 220255
el_Melissa_220310_92d21f1a34be9d21f92ec234b69b99be.arb | 220310

simplethinks · Oct 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

NM I see now that I can have a separate column with just the numbers. But it would be extremely handy if I could have the same result automatically to an adjacent column.

Thanks for all the help.

cytop · Oct 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

The next simplest way would be a UDF that takes the cell contents, chops it using the VBA SPLIT() command, again with the _ as the separator and returns the 3rd element (array index #2, as in 0, 1, 2...)

Add a new module in the VBA dev environment. Copy the following:

Code

Public Function SpecialSplit(str As String) As Variant


    Dim x As Variant


    x = Split(str, "_")
    SpecialSplit = Val(x(2))


End Function

Display More

Call it using [bfn]=specialsplit(A1)[/bfn]

imdkbj · Oct 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

You could also use mid,find for the same.
If your text string is in A1 then use =MID(A1,FIND("_",A1,4)+1,6) or replace A1.

simplethinks · Nov 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Ahhh I'm a bit lost.

I cannot run any of the functions you mentioned as I got an error. Then I tried the VB script and yet nada.
Can anyone help this poor lost soul? As you might have gathered I'm not an expert (not even a power-user of Excel).

Thanks again for all the help.

**NBVC** · Nov 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Try:

=0+TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",100)),200,100))

**S O** · Nov 15th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Try pasting this is the worksheet module:

Code

Public i As Long
Public Function FindNumbers(StringToCheck As String) As String


Parts = Split(StringToCheck, "_")


 For i = LBound(Parts) To UBound(Parts)
     If IsNumeric(Parts(i)) And Len(Parts(i)) = 6 Then
          FindNumbers = Parts(i)
           Exit For
     End If
 Next i


End Function

Display More

and use =FindNumbers("cell containing string to check")

e.g. =FindNumbers(A1)

imdkbj · Nov 18th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Look at below it's working fine.
For More, You have to share the worksheet.

[ATTACH=CONFIG]57508[/ATTACH]

imdkbj · Nov 18th 2013

Re: Extracting the first 6 numbers from a text string (that has other numbers)

Look at below pic.
it's working fine.
For More, You have to share the worksheet.

[ATTACH=CONFIG]57508[/ATTACH]

Extracting the first 6 numbers from a text string (that has other numbers)

Participate now!

Share

Users Viewing This Thread