Extract just numeric part of mixed text/number entry?

[Heidi]

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

 

[CLR]

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

 

[Marcelo]

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:

 

[Marcelo]

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

 

[Ron Coderre]

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&"0123456789")),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&"0123456789")),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]

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]

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

 

[pdgood]

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