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

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

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

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

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" 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

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

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

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