removing alpha

[Guest]

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?

 

[CLR]

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

 

[Guest]

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

=LEFT(A1,1)

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Gord Dibben]

Ron

May I suggest a small change?

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

Turns the numbers back into numbers.


Gord

 

[Rick Rothstein \(MVP - VB\)]

I have a cells that have numbers and alphas...for example, 1A, 1B,

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

What about the relative speed of my Regular Expression solution

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

 

[Gord Dibben]

Thanks Ron

I just keep learning.


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