Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying lengths.

I need to strip out all alpha letters and retain only the numeric numbers.

Either a fomula or Macro.

Many Thanks,

Bam.

On Mon, 10 May 2010 16:32:01 -0700, Bam wrote:

Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying lengths.

I need to strip out all alpha letters and retain only the numeric numbers.

Either a fomula or Macro.

Many Thanks,

Bam.


=LOOKUP(1E+307,--LEFT(A1,ROW(INDIRECT("1:99"))),
LEFT(A1,ROW(INDIRECT("1:99"))))

Change the "99" to some value that will be longer than your longest anticipated
number.

--ron

How about this simple UDF:

Public Function Numerals(rng As Range) As String
'
' gsnuxx
'
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
Numerals = sStr1
End Function

--
Gary''s Student - gsnu201002


"Bam" wrote:

Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying lengths.

I need to strip out all alpha letters and retain only the numeric numbers.

Either a fomula or Macro.

Many Thanks,

Bam.

On Mon, 10 May 2010 17:31:01 -0700, Gary''s Student
wrote:

How about this simple UDF:

Public Function Numerals(rng As Range) As String
'
' gsnuxx
'
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
Numerals = sStr1
End Function

That should work OK so long as there are numerals after the initial set of
numbers.

For example: 123ABC6GH

Your UDF -- 1236 and I suspect the OP would probably want 123
--ron

How about this simple UDF:

Public Function Numerals(rng As Range) As String
'
' gsnuxx
'
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
Numerals = sStr1
End Function

That should work OK so long as there are numerals after the initial set of
numbers.

For example: 123ABC6GH

Your UDF -- 1236 and I suspect the OP would probably want 123

How about this real simple UDF for that condition...

Public Function Numerals(Rng As Range) As Variant
Numerals = Val(Rng.Value)
End Function

--
Rick (MVP - Excel)

On Mon, 10 May 2010 22:54:11 -0400, "Rick Rothstein"
wrote:

How about this real simple UDF for that condition...

Public Function Numerals(Rng As Range) As Variant
Numerals = Val(Rng.Value)
End Function

Now the OP has both a VBA and a worksheet function solution.
--ron

See http://www.ozgrid.com/VBA/ExtractNum.htm

which can handle Decimal and negative values, or not.



--
Regards
Dave Hawley
www.ozgrid.com
"Bam" wrote in message
...
Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying
lengths.

I need to strip out all alpha letters and retain only the numeric numbers.

Either a fomula or Macro.

Many Thanks,

Bam.

See http://www.ozgrid.com/VBA/ExtractNum.htm

which can handle Decimal and negative values, or not.

Given the OP wants to grab the number which is located at the beginning of
the text, here is a shorter UDF that has the same functionality as your UDF
does for this situation...

Public Function Numerals(Rng As Range, Optional Take_decimal As Boolean, _
Optional Take_negative As Boolean) As Double
Numerals = Val(Rng.Value)
If Not Take_decimal Then Numerals = Replace(Numerals, ".", "")
If Not Take_negative Then Numerals = Replace(Numerals, "-", "")
End Function

--
Rick (MVP - Excel)



"ozgrid.com" wrote in message
...
See http://www.ozgrid.com/VBA/ExtractNum.htm

which can handle Decimal and negative values, or not.



--
Regards
Dave Hawley
www.ozgrid.com
"Bam" wrote in message
...
Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying
lengths.

I need to strip out all alpha letters and retain only the numeric
numbers.

Either a fomula or Macro.

Many Thanks,

Bam.

=LOOKUP(99^99,--("0"&MID(A1,1,ROW($1:$10000))))

--
Jacob (MVP - Excel)


"Bam" wrote:

Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying lengths.

I need to strip out all alpha letters and retain only the numeric numbers.

Either a fomula or Macro.

Many Thanks,

Bam.

I don't think you need to concatenate the "0" onto the front of the MID
function which means you can also remove a couple of parentheses. I believe
this will work...

=LOOKUP(99^99,--MID(A1,1,ROW($1:$10000)))

You can save two additional characters by using the LEFT function instead of
the MID function...

=LOOKUP(99^99,--LEFT(A1,ROW($1:$1000)))

--
Rick (MVP - Excel)



"Jacob Skaria" wrote in message
...
=LOOKUP(99^99,--("0"&MID(A1,1,ROW($1:$10000))))

--
Jacob (MVP - Excel)


"Bam" wrote:

Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying
lengths.

I need to strip out all alpha letters and retain only the numeric
numbers.

Either a fomula or Macro.

Many Thanks,

Bam.

Very Nice!!
--
Gary''s Student - gsnu201002


"Rick Rothstein" wrote:

How about this simple UDF:

Public Function Numerals(rng As Range) As String
'
' gsnuxx
'
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
Numerals = sStr1
End Function

That should work OK so long as there are numerals after the initial set of
numbers.

For example: 123ABC6GH

Your UDF -- 1236 and I suspect the OP would probably want 123

How about this real simple UDF for that condition...

Public Function Numerals(Rng As Range) As Variant
Numerals = Val(Rng.Value)
End Function

--
Rick (MVP - Excel)

.

You are right.
--
Jacob (MVP - Excel)


"Rick Rothstein" wrote:

I don't think you need to concatenate the "0" onto the front of the MID
function which means you can also remove a couple of parentheses. I believe
this will work...

=LOOKUP(99^99,--MID(A1,1,ROW($1:$10000)))

You can save two additional characters by using the LEFT function instead of
the MID function...

=LOOKUP(99^99,--LEFT(A1,ROW($1:$1000)))

--
Rick (MVP - Excel)



"Jacob Skaria" wrote in message
...
=LOOKUP(99^99,--("0"&MID(A1,1,ROW($1:$10000))))

--
Jacob (MVP - Excel)


"Bam" wrote:

Hi All,

I have a colmun of Codes, of varying lengths that contains Letters at the
end of each Code.

The letters range from 1 single letter up to a word, again of varying
lengths.

I need to strip out all alpha letters and retain only the numeric
numbers.

Either a fomula or Macro.

Many Thanks,

Bam.


.