Extracting a number in a text string

P

Pogo

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.
 
M

Marcelo

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:
 
P

Pogo

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

Marcelo

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:
 
R

RagDyeR

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.
 
P

Pogo

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top