Extracting a number in a text string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 am a casual user. Is this an embedded function within the Excel software. I
don't think I understand your response.
 
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:
 
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!
=====================================================


I am a casual user. Is this an embedded function within the Excel software.
I
don't think I understand your response.
 
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.
 
Back
Top