Extract number from text cell

C

Clare1805

New Member
Joined
Apr 14, 2008
Messages
39
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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?
 
Upvote 0
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!
 
Upvote 0
try this UDF

Code: 
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
AB
1Call 666461 has been assigned to your group666461
2CIT Customer Host/Incident/Server Software - 666592666592
3667360 - /Incident/JulieP667360
4Call to be logged with Priority 2 - System Running Slow-ref673842673842
Sheet7
Excel 2003
Cell Formulas
RangeFormula
B1=extract_nums(A1)


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

Same as Sankar's with a small variation:

Code: 
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
 
Upvote 0
pgc01 said:
Hi

Same as Sankar's with a small variation:
Click to expand...

Hi PGC,

It was a function from seiya, I have no clue how that works,:biggrin:
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?
 
Upvote 0
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. :)
 
Upvote 0
pgc01 said:
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. :)
Click to expand...


Hi PGC,

Thank you so much for those introductions and the links,
I will go through them, and start to do some experiments:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Extract Numbers and Text
Replies
9
Views
169
Peter_SSs
Peter_SSs
Obewan
  • Question
formula to extract numbers from text
2 3
Replies
25
Views
410
Peter_SSs
Peter_SSs
T
  • Question
Complex VBA to loop through each worksheet and extract text
Replies
3
Views
172
bobsan42
bobsan42
2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
815
2BLUE4U
2BLUE4U
C
  • Question
Extracting data from a worksheet and sorting into another
Replies
4
Views
276
gplhl
G

Forum statistics

Threads
1,214,833
Messages
6,121,857
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top