Extract just numeric part of mixed text/number entry?
 

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

ASAP Utilities, a free Add-in has a feature that will strip out either the
Alpha or numeric characters. It's available at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3



"Heidi" wrote:

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

Heidi, press Alt+F11

and past this VBA code
So use this function

=digitsonly(a1)

regards from Brazil



Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function


Public Function ASAPFullFileName() As String
'i.e. [c:\test\file.xls]
Application.Volatile
ASAPFullFileName = ActiveWorkbook.FullName
End Function

Public Function ASAPFileName() As String
'i.e. [file.xls]
Application.Volatile
ASAPFileName = ActiveWorkbook.Name
End Function

Public Function ASAPFilePath() As String
'i.e. [c:\test]
Application.Volatile
ASAPFilePath = ActiveWorkbook.Path
End Function

Sub ShowFileInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = "Criado em: " & f.DateCreated
MsgBox s
End Sub






"Heidi" escreveu:

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function

sorry I past more codes than you will use use just the first part
regards




"Marcelo" escreveu:

Heidi, press Alt+F11

and past this VBA code
So use this function

=digitsonly(a1)

regards from Brazil



Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function


Public Function ASAPFullFileName() As String
'i.e. [c:\test\file.xls]
Application.Volatile
ASAPFullFileName = ActiveWorkbook.FullName
End Function

Public Function ASAPFileName() As String
'i.e. [file.xls]
Application.Volatile
ASAPFileName = ActiveWorkbook.Name
End Function

Public Function ASAPFilePath() As String
'i.e. [c:\test]
Application.Volatile
ASAPFilePath = ActiveWorkbook.Path
End Function

Sub ShowFileInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = "Criado em: " & f.DateCreated
MsgBox s
End Sub






"Heidi" escreveu:

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

For a value in A1

If the numbers will always be the final characters in the cell contents, try
this:
B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),99)*1

(note: that formula returns an error if there are no numbers in the cell)


This formula finds numbers anywhere in the cell (returns zero if none):
B1:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Note: in case text wrap impacts the display, there are no spaces in those
formulas.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Heidi" wrote:

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

Wow. Thanks for the speedy reply! I'll try that. I used to have ASAP and
didn't reinstall it on my new machine. Silly me. :)

"CLR" wrote:

ASAP Utilities, a free Add-in has a feature that will strip out either the
Alpha or numeric characters. It's available at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3



"Heidi" wrote:

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

You're welcome........I've used ASAP Utilities successfully to "clean" data
in imported files for further processing........it's great.
Hope it helps for you here.........

Vaya con Dios,
Chuck, CABGx3



"Heidi" wrote:

Wow. Thanks for the speedy reply! I'll try that. I used to have ASAP and
didn't reinstall it on my new machine. Silly me. :)

"CLR" wrote:

ASAP Utilities, a free Add-in has a feature that will strip out either the
Alpha or numeric characters. It's available at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3



"Heidi" wrote:

I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it).

I want to extract just the numeric part (2.5 or 24.3) and have Excel treat
it as a number.

The letters and numbers are variable in length, so I can't just use LEFT,
RIGHT and LEN statements to trim the first/last few characters out.

Any ideas?

Thank you!
Heidi

Extract just numeric part of mixed text/number entry?
 

Hello,
What does it mean if you do this and instead of returning the numbers
it returns: #VALUE!
The code does go in a module, right?
thanks


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=547556