I have a cells that have numbers and alphas...for example, 1A, 1B, 1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will do it nicely.........

Vaya con Dios,
Chuck, CABGx3


"MBD" wrote in message
...
I have a cells that have numbers and alphas...for example, 1A, 1B,
1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

If all the values are one number and one character as in your examples, then
use the formula:

=LEFT(A1,1)
--
Gary''s Student - gsnu200727


"MBD" wrote:

I have a cells that have numbers and alphas...for example, 1A, 1B, 1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

OR
if you have one number and 2 letters
Use imagination for other options.

=IF(LEN(A1=2),LEFT(A1,1),LEFT(A1,2))

HTH
Michael M

"Gary''s Student" wrote:

If all the values are one number and one character as in your examples, then
use the formula:

=LEFT(A1,1)
--
Gary''s Student - gsnu200727


"MBD" wrote:

I have a cells that have numbers and alphas...for example, 1A, 1B, 1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

I have a cells that have numbers and alphas...for example, 1A, 1B,
1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

If the number part of your cell contents are either whole numbers or, if a
floating point number, uses a dot as the decimal point AND if the cell
contents **always** has the number part before the alpha part, then create a
macro with this code in it...

Dim S
For Each S In Selection
If Len(S) 0 Then S.Cells.Value = Val(S.Cells.Value)
Next

....then select the range you want to operate over and, finally, apply this
macro to it.

Rick

On Sun, 3 Jun 2007 15:27:00 -0700, MBD wrote:

I have a cells that have numbers and alphas...for example, 1A, 1B, 1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

If the numbers are all together (e.g. 1AB 123AB AB1234AB) , then you can use
this function:

=LOOKUP(9.99E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

If the numbers and letters are randomly distributed (e.g. ab123cd456ef78) and
you want to remove all the non-numbers, then this UDF:

--------------------------------------
Option Explicit
Function RemAlpha(str As String)
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

RemAlpha = oRegExp.Replace(str, "")
End With
End Function
--------------------------------------

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Modula and paste the
code above into the window that opens.

In some cell, enter the formula:

=RemAlpha(cell_ref)

and it will return only the digits.
--ron

On Sun, 3 Jun 2007 15:27:00 -0700, MBD wrote:

I have a cells that have numbers and alphas...for example, 1A, 1B, 1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

Slightly Simpler:

====================
Option Explicit
Function RemAlpha(str As String)
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
.Pattern = "\D"

RemAlpha = oRegExp.Replace(str, "")
End With
End Function
===========================
--ron

Ron

May I suggest a small change?

RemAlpha = oRegExp.Replace(str, "") * 1

Turns the numbers back into numbers.


Gord

On Sun, 03 Jun 2007 21:22:58 -0400, Ron Rosenfeld
wrote:

On Sun, 3 Jun 2007 15:27:00 -0700, MBD wrote:

I have a cells that have numbers and alphas...for example, 1A, 1B, 1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

Slightly Simpler:

====================
Option Explicit
Function RemAlpha(str As String)
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
.Pattern = "\D"

RemAlpha = oRegExp.Replace(str, "")
End With
End Function
===========================
--ron

I have a cells that have numbers and alphas...for example, 1A, 1B,
1C.....in
order to join this table to a corresponding table in GIS, I need to remove
the alpha leaving me with 1,2,3....any ideas?

Slightly Simpler:

====================
Option Explicit
Function RemAlpha(str As String)
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
.Pattern = "\D"

RemAlpha = oRegExp.Replace(str, "")
End With
End Function
===========================

I would think the String values would be short enough that this macro would
work fairly quickly too...

Function RemAlpha(ByVal str As String) As String
Dim X As Long
For X = 1 To Len(str)
If Mid$(str, X, 1) Like "[!0-9]" Then Mid$(str, X) = " "
Next
RemAlpha = Replace(str, " ", "")
End Function

And if we wanted to return a number instead of a string value, this macro
instead...

Function RemAlpha(ByVal str As String) As Long
Dim X As Long
For X = 1 To Len(str)
If Mid$(str, X, 1) Like "[!0-9]" Then Mid$(str, X) = " "
Next
RemAlpha = CLng(Replace(str, " ", ""))
End Function

Rick

On Sun, 03 Jun 2007 18:43:23 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Ron

May I suggest a small change?

RemAlpha = oRegExp.Replace(str, "") * 1

Turns the numbers back into numbers.


Gord

Good catch. I omitted the string to number conversion inadvertently.

Although in the version I generally use, I do it this way:

Function RemAlpha(str As String) As Long

since I'm only doing integers. If I were doing decimal numbers, the "pattern"
would be different, and the function call would be:

Function RemAlpha(str As String) As Double

I think that putting it into the Function call makes it more clear as to what
is being returned.
--ron

On Sun, 3 Jun 2007 22:18:00 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I would think the String values would be short enough that this macro would
work fairly quickly too...

Function RemAlpha(ByVal str As String) As String
Dim X As Long
For X = 1 To Len(str)
If Mid$(str, X, 1) Like "[!0-9]" Then Mid$(str, X) = " "
Next
RemAlpha = Replace(str, " ", "")
End Function

And if we wanted to return a number instead of a string value, this macro
instead...

Function RemAlpha(ByVal str As String) As Long
Dim X As Long
For X = 1 To Len(str)
If Mid$(str, X, 1) Like "[!0-9]" Then Mid$(str, X) = " "
Next
RemAlpha = CLng(Replace(str, " ", ""))
End Function

Rick

I find that frequently the variability in the data is not expressed in the
initial posting.

If we accept the premise, from his example, that the digits always come first,
and also that he wants a NUMBER returned, we could shorten the UDF even
further:

Function RemAlpha(str)
RemAlpha = Val(str)
End Function



--ron

On Mon, 04 Jun 2007 00:58:48 -0400, Ron Rosenfeld
wrote:

I find that frequently the variability in the data is not expressed in the
initial posting.

If we accept the premise, from his example, that the digits always come first,
and also that he wants a NUMBER returned, we could shorten the UDF even
further:

Function RemAlpha(str)
RemAlpha = Val(str)
End Function


Hmmm -- I now see you used that same method in your initial post.


--ron

If we accept the premise, from his example, that the digits always come
first,
and also that he wants a NUMBER returned, we could shorten the UDF even
further:

Function RemAlpha(str)
RemAlpha = Val(str)
End Function

Yes, I addressed that assumption and offered a Val function solution in my
first posting in this thread (although I structured it as a self-contained
macro as opposed to a macro-to-be-used-as-a-formula). My post here, against
your message, restricted the assumptions to the same ones you used for your
regular expression function solution.

Rick

On Mon, 4 Jun 2007 01:16:30 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

If we accept the premise, from his example, that the digits always come
first,
and also that he wants a NUMBER returned, we could shorten the UDF even
further:

Function RemAlpha(str)
RemAlpha = Val(str)
End Function

Yes, I addressed that assumption and offered a Val function solution in my
first posting in this thread (although I structured it as a self-contained
macro as opposed to a macro-to-be-used-as-a-formula). My post here, against
your message, restricted the assumptions to the same ones you used for your
regular expression function solution.

Rick

Looks like I posted at the same time acknowledging.

Why, in your UDF, do you first replace the non-digits with spaces, and then
replace the spaces?

Why not something like:

If Mid$(str, X, 1) Like "[!0-9]" Then
remalpha = remalpha & Mid$(str, X)
end if


--ron

Why, in your UDF, do you first replace the non-digits with spaces,
and then replace the spaces?

Why not something like:

If Mid$(str, X, 1) Like "[!0-9]" Then
remalpha = remalpha & Mid$(str, X)
end if

Force of habit... I come from the compiled version of VB world (newly
returned to Excel and VBA after a **long** absence) where the String values
can be **much** longer than what the typical Excel cell might hold. Repeated
concatenations tend to bog down, time-wise, as the number of them performed
increase. The technique I posted (using the statement form of Mid, as
opposed to the function form, for assignments) is very much faster overall
than simple concatenations, even when counting the relative slowness of the
single Replace function call, especially for long String values.

Rick

On Mon, 4 Jun 2007 09:36:29 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Why, in your UDF, do you first replace the non-digits with spaces,
and then replace the spaces?

Why not something like:

If Mid$(str, X, 1) Like "[!0-9]" Then
remalpha = remalpha & Mid$(str, X)
end if

Force of habit... I come from the compiled version of VB world (newly
returned to Excel and VBA after a **long** absence) where the String values
can be **much** longer than what the typical Excel cell might hold. Repeated
concatenations tend to bog down, time-wise, as the number of them performed
increase. The technique I posted (using the statement form of Mid, as
opposed to the function form, for assignments) is very much faster overall
than simple concatenations, even when counting the relative slowness of the
single Replace function call, especially for long String values.

Rick

OK, that makes sense.

What about the relative speed of my Regular Expression solution vs your Mid
solution, for those long strings.

I've come to enjoy the flexibility of regular expressions, since I was
introduced to them by Harlan Grove.
--ron

What about the relative speed of my Regular Expression solution
vs your Mid solution, for those long strings.

I haven't used regular expressions since the mid-1980's (when my work moved
its CADD system to UNIX), so I am not sure I would know how to construct a
proper test for them. Besides, in the compiled world of VB, references to
scripting add on objects tended to be slow in and of themselves, so my gut
tells me that the Mid statement solution would be faster... again, that is
in the compiled VB world. I get the impression that VBA is much more
tolerant (speed-wise) of scripting add ons, so results from a test performed
in the compiled version of VB might not correctly depict their performance
in the VBA world.

I've come to enjoy the flexibility of regular expressions, since I was
introduced to them by Harlan Grove.

I remember working with regular expressions way back when... the thing I
always marveled over was my ability to construct complex regular expressions
that I had trouble reading in as little as one hour later!

Rick

On Mon, 4 Jun 2007 10:54:23 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

What about the relative speed of my Regular Expression solution
vs your Mid solution, for those long strings.

I haven't used regular expressions since the mid-1980's (when my work moved
its CADD system to UNIX), so I am not sure I would know how to construct a
proper test for them. Besides, in the compiled world of VB, references to
scripting add on objects tended to be slow in and of themselves, so my gut
tells me that the Mid statement solution would be faster... again, that is
in the compiled VB world. I get the impression that VBA is much more
tolerant (speed-wise) of scripting add ons, so results from a test performed
in the compiled version of VB might not correctly depict their performance
in the VBA world.

I've come to enjoy the flexibility of regular expressions, since I was
introduced to them by Harlan Grove.

I remember working with regular expressions way back when... the thing I
always marveled over was my ability to construct complex regular expressions
that I had trouble reading in as little as one hour later!

Rick

Instead of the CreateObject method, one can set a reference under
Tools/References.

The latter has the advantage of providing help for the allowable arguments and
is what I do for my own work. However, it adds complexity to the explanations
for the one-off solutions requested here, and someone else posted that the time
savings is minimal.
--ron

What about the relative speed of my Regular Expression solution
vs your Mid solution, for those long strings.

I haven't used regular expressions since the mid-1980's (when my work
moved
its CADD system to UNIX), so I am not sure I would know how to construct a
proper test for them. Besides, in the compiled world of VB, references to
scripting add on objects tended to be slow in and of themselves, so my gut
tells me that the Mid statement solution would be faster... again, that is
in the compiled VB world. I get the impression that VBA is much more
tolerant (speed-wise) of scripting add ons, so results from a test
performed
in the compiled version of VB might not correctly depict their performance
in the VBA world.

I've come to enjoy the flexibility of regular expressions, since I was
introduced to them by Harlan Grove.

I remember working with regular expressions way back when... the thing I
always marveled over was my ability to construct complex regular
expressions
that I had trouble reading in as little as one hour later!

Rick

Instead of the CreateObject method, one can set a reference under
Tools/References.

The latter has the advantage of providing help for the allowable arguments
and
is what I do for my own work. However, it adds complexity to the
explanations
for the one-off solutions requested here, and someone else posted that the
time
savings is minimal.

True, you could incorporate the reference that way, but I don't think that
changes the way the compiled version of VB handles calls to the script
engine... I think it still temporarily "drops out" of the compiled code to
interface with the script engine... that action, and the interaction to pass
values back and forth, is what I think slows down the compiled program when
it uses scripts. VBA, not being compiled, does not appear to have this
problem; or, if it does, its impact is less noticeable than in compiled VB.
Of course, with a regular expression engine, the time saved by the regular
expression parsing may offset the script interaction slowness; but as a
rule, compiled VB'ers tend to stay away from scripts.

Rick

Thanks Ron

I just keep learning.


Gord

On Sun, 03 Jun 2007 22:21:01 -0400, Ron Rosenfeld
wrote:

On Sun, 03 Jun 2007 18:43:23 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Ron

May I suggest a small change?

RemAlpha = oRegExp.Replace(str, "") * 1

Turns the numbers back into numbers.


Gord

Good catch. I omitted the string to number conversion inadvertently.

Although in the version I generally use, I do it this way:

Function RemAlpha(str As String) As Long

since I'm only doing integers. If I were doing decimal numbers, the "pattern"
would be different, and the function call would be:

Function RemAlpha(str As String) As Double

I think that putting it into the Function call makes it more clear as to what
is being returned.
--ron

On Mon, 04 Jun 2007 11:38:13 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Thanks Ron

I just keep learning.


Gord


Me too!
--ron

On Mon, 4 Jun 2007 14:35:09 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

True, you could incorporate the reference that way, but I don't think that
changes the way the compiled version of VB handles calls to the script
engine... I think it still temporarily "drops out" of the compiled code to
interface with the script engine... that action, and the interaction to pass
values back and forth, is what I think slows down the compiled program when
it uses scripts. VBA, not being compiled, does not appear to have this
problem; or, if it does, its impact is less noticeable than in compiled VB.
Of course, with a regular expression engine, the time saved by the regular
expression parsing may offset the script interaction slowness; but as a
rule, compiled VB'ers tend to stay away from scripts.

Rick

Well, I have no experience with compiled VB, only VBA.

Programming is a hobby with me -- not a vocation. I have a familiarity with
BASIC (I was a student at Dartmouth when it was being developed); Pascal, JCL,
Assembly language and now VBA. This familiarity was provoked by various needs
at the times.
--ron

Well, I have no experience with compiled VB, only VBA.

And my experience with VBA is quite limited (as I am sure you have deduced
from some of my postings<g).


Programming is a hobby with me -- not a vocation.

Same here. I started on a Texas Instrument 99/4 back in 1981 and have been
hooked on programming ever since. As for professionally, I was a Civil
Engineer (Road Design; retired now) for the first half of my career and fell
into "professional" (I use that term very loosely) programming when I
started developing "helper" programs to simplify some of the more routine,
but lengthy, processes we engineers were required to follow. Eventually, I
joined the CADD development group (covering the second half of my career)
when it was formed and, as part of the work I did there, created "helper"
programs for that venue. I am completely self-taught, which will explain
some of the large gaps in my programming knowledge; still, I am able to
wrestle a program into shape when needed.


I have a familiarity with BASIC (I was a student at Dartmouth
when it was being developed); Pascal, JCL, Assembly language
and now VBA.

You sound like you may be older than I am (I am on the young side of 60).
With me it was mostly BASIC and VB, but I have also worked with FORTRAN,
C/C++ and various Unix scripting languages.


This familiarity was provoked by various needs at the times.

Ditto.


Rick

On Mon, 4 Jun 2007 15:51:34 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

You sound like you may be older than I am (I am on the young side of 60).
With me it was mostly BASIC and VB, but I have also worked with FORTRAN,
C/C++ and various Unix scripting languages.

Well, I'm pushing 60, too. But from the high side. Almost ready for Medicare.
Someone has written that this sort of activity keeps the brain "young". Use it
or lose it, I guess.

Best,
--ron