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

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

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

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

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