Removing Trailing Alpha?

  • Thread starter Thread starter Guest
  • Start date Start date
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,
 
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
 
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.
 
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
 
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))
 
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
 
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))
 
Back
Top