Prev: Ranking from one sheet to another
Next: Excel auto accumulation formulas
From: Bam on 10 May 2010 19:32
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.


From: Ron Rosenfeld on 10 May 2010 20:28
On Mon, 10 May 2010 16:32:01 -0700, Bam <Bam(a)discussions.microsoft.com> 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
From: Gary''s Student on 10 May 2010 20:31
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.
>
>
From: Ron Rosenfeld on 10 May 2010 21:14
On Mon, 10 May 2010 17:31:01 -0700, Gary''s Student
<GarysStudent(a)discussions.microsoft.com> 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
From: Rick Rothstein on 10 May 2010 22:54
>>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)

 |  Next  |  Last
Pages: 1 2 3
Prev: Ranking from one sheet to another
Next: Excel auto accumulation formulas