Extracting a number in a text string

I need to extract a number from a text string. For example, Cryo Technician
$100.00. I want to extract the $100 rate and use it in another function,
i.e., Rate x hours.

I hope I made myself clear. Thanks in advance.

Hi Pogo,

try this module and use
=digitsonly(a1)

***********************************
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
*********************************************
hth
regards from Brazil
Marcelo



"Pogo" escreveu:

> I need to extract a number from a text string. For example, Cryo Technician
> $100.00. I want to extract the $100 rate and use it in another function,
> i.e., Rate x hours.
>
> I hope I made myself clear. Thanks in advance.

I am a casual user. Is this an embedded function within the Excel software. I
don't think I understand your response.


"Marcelo" wrote:

> Hi Pogo,
>
> try this module and use
> =digitsonly(a1)
>
> ***********************************
> 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
> *********************************************
> hth
> regards from Brazil
> Marcelo
>
>
>
> "Pogo" escreveu:
>
> > I need to extract a number from a text string. For example, Cryo Technician
> > $100.00. I want to extract the $100 rate and use it in another function,
> > i.e., Rate x hours.
> >
> > I hope I made myself clear. Thanks in advance.

Hi Pogo,

ok, this is not an excel function, to it run, you must copy the module above
to do it, press Alt+F11 (excel will open a VBA page) click on the INSERT
menu and chose MODULE,

COPY and PASTE the Public Function there, close the vba page and use this
User function on your spreadsheet.

hth
regards from Brazil
Marcelo

"Pogo" escreveu:

> I am a casual user. Is this an embedded function within the Excel software. I
> don't think I understand your response.
>
>
> "Marcelo" wrote:
>
> > Hi Pogo,
> >
> > try this module and use
> > =digitsonly(a1)
> >
> > ***********************************
> > 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
> > *********************************************
> > hth
> > regards from Brazil
> > Marcelo
> >
> >
> >
> > "Pogo" escreveu:
> >
> > > I need to extract a number from a text string. For example, Cryo Technician
> > > $100.00. I want to extract the $100 rate and use it in another function,
> > > i.e., Rate x hours.
> > >
> > > I hope I made myself clear. Thanks in advance.

If you would prefer a formula, since this *is* the 'functions' group, you
could try this:

With your string in A1, try in B1:

=--MID(A1,FIND("$",A1),25)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Pogo" <[email protected]> wrote in message
news:[email protected]...
I am a casual user. Is this an embedded function within the Excel software.
I
don't think I understand your response.


"Marcelo" wrote:

> Hi Pogo,
>
> try this module and use
> =digitsonly(a1)
>
> ***********************************
> 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
> *********************************************
> hth
> regards from Brazil
> Marcelo
>
>
>
> "Pogo" escreveu:
>
> > I need to extract a number from a text string. For example, Cryo
Technician
> > $100.00. I want to extract the $100 rate and use it in another function,
> > i.e., Rate x hours.
> >
> > I hope I made myself clear. Thanks in advance.

Thanks RagDyeR
This is what I wanted to do
From: Technician$125.21 To: 125.21 and I got it.

Both of these formulas worked:

=--MID(G2,FIND("$",G2),25)
=RIGHT(G4,LEN(G4)-FIND("$",G4,1))

Thanks RagDyeR

Marcelo I tried. Copied the formula into VBA Module but I couldn't noodle
it out. Thanks for trying.

"RagDyeR" wrote:

> If you would prefer a formula, since this *is* the 'functions' group, you
> could try this:
>
> With your string in A1, try in B1:
>
> =--MID(A1,FIND("$",A1),25)
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
>
> "Pogo" <[email protected]> wrote in message
> news:[email protected]...
> I am a casual user. Is this an embedded function within the Excel software.
> I
> don't think I understand your response.
>
>
> "Marcelo" wrote:
>
> > Hi Pogo,
> >
> > try this module and use
> > =digitsonly(a1)
> >
> > ***********************************
> > 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
> > *********************************************
> > hth
> > regards from Brazil
> > Marcelo
> >
> >
> >
> > "Pogo" escreveu:
> >
> > > I need to extract a number from a text string. For example, Cryo
> Technician
> > > $100.00. I want to extract the $100 rate and use it in another function,
> > > i.e., Rate x hours.
> > >
> > > I hope I made myself clear. Thanks in advance.
>
>
>