Hi everyone,

Say I have this:

T1
T4
T20
T100
..
..

I want to create a col that has it like this:
1
4
20
100
..
..

any simple way to do this?

Mike

If your list starts in A1...

In B1 enter
=MID($A1,2,LEN($A1)-1)

and copy down as needed.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

If your list starts in A1...

In B1 enter
=MID($A1,2,LEN($A1)-1)

and copy down as needed

Another way to do this...

=REPLACE(A1,1,1,"")

Note: If you are copying down, there is no need to make the column reference
absolute.

Rick Rothstein (MVP - Excel)

=RIGHT(A1,LEN(A1)-1) entered in B1

Copy down to B4

Select B1:B4 and copypaste specialvalues onto column A

Delete column B


Gord Dibben MS Excel MVP


On Mon, 13 Jun 2011 10:51:59 -0700 (PDT), Mike wrote:

Hi everyone,

Say I have this:

T1
T4
T20
T100
.
.

I want to create a col that has it like this:
1
4
20
100
.
.

any simple way to do this?

Mike

On Jun 13, 10:51*am, Mike wrote:
Say I have this:
T1
T4
T20
T100

I want to create a col that has it like this:
1
4
20
100

any simple way to do this?

Define "simple". One way.... Put the following into a parallel cell
and copy down, assuming "T1" is in A1:

=MID(A1,2,999)

Note: That means 1, 4, 20 etc are strings. If you want numeric
values:

=--MID(A1,2,999)

The double-negative (--) converts text to numeric.

Another way.... Select the cells with T1, T4, T20 etc, click Date
Text To Columns. In the first menu, select Fixed Width, then click
Next. In the second menu, click on the data preview "ruler" to create
a separator between the first character and the rest of the text; then
click Next. In the final menu, select the first column and click "Do
not import (skip)". If you want the 1, 4 20 etc to be text, select
the second column and click Text. In either case, then press Finish.

Rick Rothstein formulated the question :
Note: If you are copying down, there is no need to make the column reference
absolute.

Agreed! It's just a habit I've fallen into as a result of most often
creating formulas for parsing a cell's contents across several columns.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc