Removing Trailing Alpha?

G

Guest

I have a column of data that looks like the following:

1501
1501B
1520
1530
1530B
11
11B
120
120C
120D

I want to pull the numerals from the cells and somehow "discard" the alpha
portion of the datum therein. I've tried, RIGHT, 1) with the intention of
doing a sort; but that pulls the numerics if there is no alpha. I've tried
Find and Replace *B with * and it puts a handy little star in place of the
orginal datum. I've messed around with MID, Cell, 1,4) but that doesn't
impact the 3 numeral - 1 alpha combinations.

I'm assuming there is an easy way of doing this. I'd appreciate your help
with this problem. Thanks,
 
R

Rick Rothstein \(MVP - VB\)

I have a column of data that looks like the following:
1501
1501B
1520
1530
1530B
11
11B
120
120C
120D

I want to pull the numerals from the cells and somehow "discard" the alpha
portion of the datum therein. I've tried, RIGHT, 1) with the intention of
doing a sort; but that pulls the numerics if there is no alpha. I've
tried
Find and Replace *B with * and it puts a handy little star in place of the
orginal datum. I've messed around with MID, Cell, 1,4) but that doesn't
impact the 3 numeral - 1 alpha combinations.
Click to expand...

You might try something like this...

=IF(ISNUMBER(A14),A14,--LEFT(A14,LEN(A14)-1))

Rick
 
D

Dave Peterson

With the data in A1:Axx, you could use a helper column:

=if(isnumber(-right(a1,1)),a1,--left(a1,len(a1)-1))

the -- stuff converts the text (from =left()), back to a real number.
 
G

Guest

Try this UDF:

Function numbit(r As Range)
Dim s As String
s = r.Value

For ll = 1 To 47
s = Replace(s, Chr(ll), "")
Next

For ll = 58 To 255
s = Replace(s, Chr(ll), "")
Next

numbit = --s
End Function

put =numbit(A1) in B1 and copy down to see:

1501 1501
1501B 1501
1520 1520
1530 1530
1530B 1530
11 11
11B 11
120 120
120C 120
120D 120
 
G

Guest

if there is only one alpha character at the end (and there's no trailing
spaces in the data), you could try

=--IF(ISNUMBER(--A1),A1,LEFT(A1,LEN(A1)-1))
 
G

Guest

This works but there's a hitch. I have many 001, 020, 045, type records in
the file. It's converting these to 1, 20, 45 respectively. I'm building a
load file to move data from a legacy system to our upcoming system. The load
program is expecting these zeros to be there.

Is there some way to do what you've suggested below but to bring the leading
zeros into the column as well?

Thanks
 
R

Ragdyer

Just use Dave's formula *without* the double negative.

=IF(ISNUMBER(-RIGHT(A1)),A1,LEFT(A1,LEN(A1)-1))

Since it *already* has the single negative included.

You can revise any of the others to remove the double negative, and add the
single negative:

=IF(ISNUMBER(-A1),A1,LEFT(A1,LEN(A1)-1))
 
G

Guest

Try leaving out the first double negative.
=IF(ISNUMBER(--A1),A1,LEFT(A1,LEN(A1)-1))
 
Top