Extract number from text cell

[Clare1805]

I have an extract from a call logging system; one of the columns is a description of the call (this column is extracted in Text format). Within some of the descriptions is a reference number which I need to extract (sadly this number isn't kept in its own field) - is there any way to determine whether a cell has numbers in it, and if so is there a way to pull them out into a seperate column? (preferably using a formula as I'm no good at VBA!)

Hope this makes sense and thanks in advance for your help!

 

[sanrv1f]

where are those numbers placed within the cell? at the beginning or at the end or in the middle?

could you post some sample data and expected results?

 

[Clare1805]

Thanks for the quick reply - The description cell is free text for the call agents therefore the reference number (sadly and very frustratingly!) could be anywhere within the text eg...

<TABLE style="WIDTH: 380pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=507 border=0 x:str><COLGROUP><COL style="WIDTH: 380pt; mso-width-source: userset; mso-width-alt: 18541" width=507><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 380pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=507 height=17>Call 666461 has been assigned to your group</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>CIT Customer Host/Incident/Server Software - 666592</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>667360 - /Incident/JulieP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Call to be logged with Priority 2 - System Running Slow-ref673842.</TD></TR></TBODY></TABLE>

As you can see the ref number I need to extract is always 6 characters long. Thanks again!

 

[sanrv1f]

try this UDF

Function extract_nums(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d{6})"
    If .test(txt) Then
        extract_nums = .Execute(txt)(0).submatches(0)
    End If
End With
End Function

Excel Workbook
	A	B
1	Call 666461 has been assigned to your group	666461
2	CIT Customer Host/Incident/Server Software - 666592	666592
3	667360 - /Incident/JulieP	667360
4	Call to be logged with Priority 2 - System Running Slow-ref673842	673842
Sheet7

Excel 2003

Cell Formulas
Range	Formula
B1	=extract_nums(A1)

the above code is from here -> http://www.mrexcel.com/forum/showthread.php?t=399880

 

[pgc01]

Hi

Same as Sankar's with a small variation:

Function extract_nums(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\d{6}"
    If .test(txt) Then extract_nums = .Execute(txt)(0)
End With
End Function

 

[sanrv1f]

Hi

Same as Sankar's with a small variation:

Hi PGC,

It was a function from seiya, I have no clue how that works,
the only thing I was able to undestand is that '\d' stands for a numeric digit,

could you help me understand what is 'Scripting.Dictionary' and 'VBScript.Regexp', and how that works?

 

[pgc01]

Hi Sankar

1 -
The regular expressions are a very powerful tool to perform text pattern recognition. They've been around for 30 years+ so it's a stable tool (although you may find differences between implementations). If you google for "regular expressions" you'll find millions of hits.

There is a nice introduction here:

http://www.aivosto.com/vbtips/regex.html

2 -
The dictionary object is simply like an array where you can access the elements via a key, like a Collection. It is, in fact, very similar to a Collection. It has some advantages, like a .Exists() method, that tells you if an element exists (the collection doesn't), and you can get arrays with all the keys or all the elements, instead of having to loop through all the elements like the collection.

This is the documentation:

http://msdn.microsoft.com/en-us/library/x4k5wbx4(VS.85).aspx

Just check the properties and the methods.

You'll be an expert in Dictionaries in 15 minutes, not even close with the RegEx's.

 

[sanrv1f]

Hi Sankar

1 -
The regular expressions are a very powerful tool to perform text pattern recognition. They've been around for 30 years+ so it's a stable tool (although you may find differences between implementations). If you google for "regular expressions" you'll find millions of hits.

There is a nice introduction here:

http://www.aivosto.com/vbtips/regex.html

2 -
The dictionary object is simply like an array where you can access the elements via a key, like a Collection. It is, in fact, very similar to a Collection. It has some advantages, like a .Exists() method, that tells you if an element exists (the collection doesn't), and you can get arrays with all the keys or all the elements, instead of having to loop through all the elements like the collection.

This is the documentation:

http://msdn.microsoft.com/en-us/library/x4k5wbx4(VS.85).aspx

Just check the properties and the methods.

You'll be an expert in Dictionaries in 15 minutes, not even close with the RegEx's.

Hi PGC,

Thank you so much for those introductions and the links,
I will go through them, and start to do some experiments

 

[barry houdini]

......preferably using a formula as I'm no good at VBA!......

With data in A1 try this formula in B1

=LOOKUP(10^6,MID(SUBSTITUTE(SUBSTITUTE(A1," ","x"),".","x"),ROW(INDIRECT("1:"&LEN(A1)-5)),6)+0)

 

[sanrv1f]

With data in A1 try this formula in B1

=LOOKUP(10^6,MID(SUBSTITUTE(SUBSTITUTE(A1," ","x"),".","x"),ROW(INDIRECT("1:"&LEN(A1)-5)),6)+0)

Barry, thats magicalray: