Doing this in Excel?

[Mike]

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

 

[GS]

If your list starts in A1...

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

and copy down as needed.

 

[Rick Rothstein]

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)

 

[Gord Dibben]

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

Copy down to B4

Select B1:B4 and copy>paste special>values onto column A

Delete column B


Gord Dibben MS Excel MVP

 

[joeu2004]

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.

 

[GS]

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.