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.

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.

ConfusedNHouston wrote:

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,

--

Dave Peterson

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

--
Gary''s Student - gsnu200726

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


"ConfusedNHouston" wrote:

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,

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

"JMB" wrote:

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


"ConfusedNHouston" wrote:

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,

Hi

Try
=IF(RIGHT(A1)"9",SUBSTITUTE(A1,RIGHT(A1),""),A1)

--
Regards

Roger Govier


"ConfusedNHouston" wrote in
message ...
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

"JMB" wrote:

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


"ConfusedNHouston" wrote:

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,

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


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ConfusedNHouston" wrote in
message ...
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

"JMB" wrote:

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


"ConfusedNHouston" wrote:

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,

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


"ConfusedNHouston" wrote:

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

"JMB" wrote:

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


"ConfusedNHouston" wrote:

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,