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

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

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

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