Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting a number in a text string
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting a number in a text string
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting a number in a text string
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting a number in a text string
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting a number in a text string
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
edit text string in column B | Excel Discussion (Misc queries) | |||
Extracting text from text string | Excel Worksheet Functions | |||
Displays the number in text. (One thousand two hundred thirty four | Excel Worksheet Functions | |||
Setting the number of decimal places for a text box. | Excel Discussion (Misc queries) | |||
How do I look up a number within a string of text | Excel Worksheet Functions |