Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
hi,
how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
With a macro:-
Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Select the cells to convert and run this Mike "peyman" wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. You can use a UDF. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this UDF, enter =ExtrNums(cell_ref) into some cell. =============================== Option Explicit Function ExtrNums(str As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = "\D" ExtrNums = re.Replace(str, "") End Function ================================ --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
hi Mike,
it just removes dash "-" from string!! not letters. "Mike H" wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Select the cells to convert and run this Mike "peyman" wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
thank you so much.that's great!!!!!!!!!!
"Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 10:28:00 -0700, peyman wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. You can use a UDF. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this UDF, enter =ExtrNums(cell_ref) into some cell. =============================== Option Explicit Function ExtrNums(str As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = "\D" ExtrNums = re.Replace(str, "") End Function ================================ --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
Another way.
I'm a bit cofused why the previous method only removed th dash but try this Sub anotherway() For Each cell In Selection For a = 1 To Len(cell) Select Case Mid(cell, a, 1) Case "0" To "9" newstring = newstring & Mid(cell, a, 1) End Select Next a cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next cell End Sub Mike "peyman" wrote: hi Mike, it just removes dash "-" from string!! not letters. "Mike H" wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Select the cells to convert and run this Mike "peyman" wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
thanx mike,it works.I really appreciate it.
"Mike H" wrote: Another way. I'm a bit cofused why the previous method only removed th dash but try this Sub anotherway() For Each cell In Selection For a = 1 To Len(cell) Select Case Mid(cell, a, 1) Case "0" To "9" newstring = newstring & Mid(cell, a, 1) End Select Next a cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next cell End Sub Mike "peyman" wrote: hi Mike, it just removes dash "-" from string!! not letters. "Mike H" wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Select the cells to convert and run this Mike "peyman" wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
Ron,
Excellent. any chance of a quick rundown on how it works or a web reference please? Mike H "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 10:28:00 -0700, peyman wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. You can use a UDF. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this UDF, enter =ExtrNums(cell_ref) into some cell. =============================== Option Explicit Function ExtrNums(str As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = "\D" ExtrNums = re.Replace(str, "") End Function ================================ --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Tue, 21 Aug 2007 11:18:02 -0700, Mike H
wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Your For/Next loop can be simplified: For a = 1 To Len(cell) If Mid(cell, a, 1) Like "#" Then newstring = newstring & Mid(cell, a, 1) End If Next --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
You're welcome. Glad to help. Thanks for the feedback.
On Tue, 21 Aug 2007 11:32:00 -0700, peyman wrote: thank you so much.that's great!!!!!!!!!! "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 10:28:00 -0700, peyman wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. You can use a UDF. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this UDF, enter =ExtrNums(cell_ref) into some cell. =============================== Option Explicit Function ExtrNums(str As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = "\D" ExtrNums = re.Replace(str, "") End Function ================================ --ron --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
hi Mike,
the problem is , it removes the zero at the begining of a numbers after pulling out them,like: aab0125 turns to 125 instead of 0125 "Mike H" wrote: Another way. I'm a bit cofused why the previous method only removed th dash but try this Sub anotherway() For Each cell In Selection For a = 1 To Len(cell) Select Case Mid(cell, a, 1) Case "0" To "9" newstring = newstring & Mid(cell, a, 1) End Select Next a cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next cell End Sub Mike "peyman" wrote: hi Mike, it just removes dash "-" from string!! not letters. "Mike H" wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Select the cells to convert and run this Mike "peyman" wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
hi Ron,
the problem in Mikes macro is , it removes the zero at the begining of a numbers after pulling out them,like: aab0125 turns to 125 instead of 0125 "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 11:18:02 -0700, Mike H wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Your For/Next loop can be simplified: For a = 1 To Len(cell) If Mid(cell, a, 1) Like "#" Then newstring = newstring & Mid(cell, a, 1) End If Next --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
Hi,
It doesn't I suspect the zero is a letter "o" or "O" Mike "peyman" wrote: hi Mike, the problem is , it removes the zero at the begining of a numbers after pulling out them,like: aab0125 turns to 125 instead of 0125 "Mike H" wrote: Another way. I'm a bit cofused why the previous method only removed th dash but try this Sub anotherway() For Each cell In Selection For a = 1 To Len(cell) Select Case Mid(cell, a, 1) Case "0" To "9" newstring = newstring & Mid(cell, a, 1) End Select Next a cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next cell End Sub Mike "peyman" wrote: hi Mike, it just removes dash "-" from string!! not letters. "Mike H" wrote: With a macro:- Sub removeletters() For Each cell In Selection For a = 1 To Len(cell) If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _ Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _ newstring = newstring & Mid(cell, a, 1) Next cell.Value = newstring ActiveCell.NumberFormat = "General" newstring = "" Next End Sub Select the cells to convert and run this Mike "peyman" wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
Mike,
It uses "Regular Expressions" which is a feature included with VBA. The CreateObject method sets up the reference to Microsoft VBScript Regular Expressions 5.5. Instead of that method, one can also select Tools/References (from the VBEditor top menu) and select that as a reference. This latter method has the advantage that properties will pop up when you are writing the macro, as they do for other objects. The Pattern "\D" means "match every character that is not a digit (not 0..9). The replace method then looks at "str" and replaces every match with nothing (""), thereby removing all the non-digits. http://support.microsoft.com/default...02&Product=vbb http://msdn2.microsoft.com/en-us/library/1400241x.aspx On Tue, 21 Aug 2007 11:36:00 -0700, Mike H wrote: Ron, Excellent. any chance of a quick rundown on how it works or a web reference please? Mike H "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 10:28:00 -0700, peyman wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. You can use a UDF. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this UDF, enter =ExtrNums(cell_ref) into some cell. =============================== Option Explicit Function ExtrNums(str As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = "\D" ExtrNums = re.Replace(str, "") End Function ================================ --ron --ron |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
Thanks Ron,
I'm only on my 4th read of John Walkenbach's 2003 bible so perhaps some way off this yet. Mike "Ron Rosenfeld" wrote: Mike, It uses "Regular Expressions" which is a feature included with VBA. The CreateObject method sets up the reference to Microsoft VBScript Regular Expressions 5.5. Instead of that method, one can also select Tools/References (from the VBEditor top menu) and select that as a reference. This latter method has the advantage that properties will pop up when you are writing the macro, as they do for other objects. The Pattern "\D" means "match every character that is not a digit (not 0..9). The replace method then looks at "str" and replaces every match with nothing (""), thereby removing all the non-digits. http://support.microsoft.com/default...02&Product=vbb http://msdn2.microsoft.com/en-us/library/1400241x.aspx On Tue, 21 Aug 2007 11:36:00 -0700, Mike H wrote: Ron, Excellent. any chance of a quick rundown on how it works or a web reference please? Mike H "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 10:28:00 -0700, peyman wrote: hi, how can I pull out numbers from a string?like: aa012985 to 012985 12ab-059 to 12059 the letters or characters might be either at the first, middle or at the end of a string. thanx in advance. You can use a UDF. To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this UDF, enter =ExtrNums(cell_ref) into some cell. =============================== Option Explicit Function ExtrNums(str As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = "\D" ExtrNums = re.Replace(str, "") End Function ================================ --ron --ron |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Tue, 21 Aug 2007 12:16:02 -0700, Mike H
wrote: Thanks Ron, I'm only on my 4th read of John Walkenbach's 2003 bible so perhaps some way off this yet. Mike Regular expressions are extremely powerful tools for string manipulation. Harlan Grove turned me on to them, and I've found them quite worthwhile, although I'm still a novice in their use. --ron |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
hi Ron, Can we do it reverse.pulling out only letters. aa125df36 to aadf???? thank you "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 12:16:02 -0700, Mike H wrote: Thanks Ron, I'm only on my 4th read of John Walkenbach's 2003 bible so perhaps some way off this yet. Mike Regular expressions are extremely powerful tools for string manipulation. Harlan Grove turned me on to them, and I've found them quite worthwhile, although I'm still a novice in their use. --ron |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Tue, 21 Aug 2007 15:12:01 -0700, peyman
wrote: hi Ron, Can we do it reverse.pulling out only letters. aa125df36 to aadf???? thank you It's pretty simple. In the code I posted, you just need to change the "\D", which selects all non-digits for removal, to a "\d" which selects all digits for removal. But for more flexibility, you could include the arguments for what to find, and what to replace it with, in the function code. For example, with this UDF: ===================================== Option Explicit Function ReSub(str As String, FindText As String, ReplaceWith As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = FindText ReSub = re.Replace(str, ReplaceWith) End Function ====================================== You could use this formula to replace all digits with a null string, resulting in pulling out only letters: =resub(A1,"\d","") If you wanted to pull out only digits, then: =resub(A1,"\D","") If you wanted to replace the digits with a tilde, you could use: =resub(A1,"\d","~") If you wanted to replace all of the letters with the word Peyman, with leading and ending spaces, then: =resub(A6,"\D"," Peyman ") And many other solutions --ron |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
thanx Ron.It's excellent
"Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 15:12:01 -0700, peyman wrote: hi Ron, Can we do it reverse.pulling out only letters. aa125df36 to aadf???? thank you It's pretty simple. In the code I posted, you just need to change the "\D", which selects all non-digits for removal, to a "\d" which selects all digits for removal. But for more flexibility, you could include the arguments for what to find, and what to replace it with, in the function code. For example, with this UDF: ===================================== Option Explicit Function ReSub(str As String, FindText As String, ReplaceWith As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = FindText ReSub = re.Replace(str, ReplaceWith) End Function ====================================== You could use this formula to replace all digits with a null string, resulting in pulling out only letters: =resub(A1,"\d","") If you wanted to pull out only digits, then: =resub(A1,"\D","") If you wanted to replace the digits with a tilde, you could use: =resub(A1,"\d","~") If you wanted to replace all of the letters with the word Peyman, with leading and ending spaces, then: =resub(A6,"\D"," Peyman ") And many other solutions --ron |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Tue, 21 Aug 2007 16:56:02 -0700, peyman
wrote: thanx Ron.It's excellent You should look at some of the references I gave Mike to learn about constructing regular expressions. There are also many other ways to use them. --ron |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
hi Ron,
where should I locate the following commands in the UDF codes? =resub(A1,"\d","") or =resub(A1,"\D",""), =resub.... and what does A1 or A6 stand for? thanx "Ron Rosenfeld" wrote: On Tue, 21 Aug 2007 15:12:01 -0700, peyman wrote: hi Ron, Can we do it reverse.pulling out only letters. aa125df36 to aadf???? thank you It's pretty simple. In the code I posted, you just need to change the "\D", which selects all non-digits for removal, to a "\d" which selects all digits for removal. But for more flexibility, you could include the arguments for what to find, and what to replace it with, in the function code. For example, with this UDF: ===================================== Option Explicit Function ReSub(str As String, FindText As String, ReplaceWith As String) Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = FindText ReSub = re.Replace(str, ReplaceWith) End Function ====================================== You could use this formula to replace all digits with a null string, resulting in pulling out only letters: =resub(A1,"\d","") If you wanted to pull out only digits, then: =resub(A1,"\D","") If you wanted to replace the digits with a tilde, you could use: =resub(A1,"\d","~") If you wanted to replace all of the letters with the word Peyman, with leading and ending spaces, then: =resub(A6,"\D"," Peyman ") And many other solutions --ron |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Wed, 22 Aug 2007 09:12:00 -0700, peyman
wrote: hi Ron, where should I locate the following commands in the UDF codes? =resub(A1,"\d","") or =resub(A1,"\D",""), =resub.... I don't know what you mean by that question. =resub(A1,"\d","") is a function you enter in a worksheet cell. and what does A1 or A6 stand for? A1 "stands for" the worksheet cell that is in the first row and first column of your sheet (upper left corner). A6 "stands for" the worksheet cell located in the 6th row of the first column. In my first post, I used cell_ref for that. But I thought that by now you would realize that these are just arbitrary cell references to where I happened to have the test string. If you are using the R1C1 reference system, then A1 is the same as R1C1, and A6 is the same R6C1. --ron |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
Sorry Ron, I got it.thanx
"Ron Rosenfeld" wrote: On Wed, 22 Aug 2007 09:12:00 -0700, peyman wrote: hi Ron, where should I locate the following commands in the UDF codes? =resub(A1,"\d","") or =resub(A1,"\D",""), =resub.... I don't know what you mean by that question. =resub(A1,"\d","") is a function you enter in a worksheet cell. and what does A1 or A6 stand for? A1 "stands for" the worksheet cell that is in the first row and first column of your sheet (upper left corner). A6 "stands for" the worksheet cell located in the 6th row of the first column. In my first post, I used cell_ref for that. But I thought that by now you would realize that these are just arbitrary cell references to where I happened to have the test string. If you are using the R1C1 reference system, then A1 is the same as R1C1, and A6 is the same R6C1. --ron |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
pulling out Numbers
On Wed, 22 Aug 2007 10:06:02 -0700, peyman
wrote: Sorry Ron, I got it.thanx No need to apologize. Sometimes I assume mind reading abilities where none exists :-)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for pulling only numbers but not text from another cell | Excel Worksheet Functions | |||
function for pulling certain numbers from a cell? | Excel Worksheet Functions | |||
Pulling the largest numbers | Excel Worksheet Functions | |||
pulling numbers from hhh:mm:ss | Excel Worksheet Functions | |||
Pulling larger numbers from group | Excel Worksheet Functions |