We have cells that are text and 1 2 3 .....110

the problem is now they want them as
001
002
099
100


can can the 1 2 3 98 99 be converted to leading 0's automatically

this wouldn't be too bad but it also has visit no's

so I have

ID VISIT
1_____1
1_____2
1_____3
1_____4
2_____1
..
..
..
99_____3
99_____4

ect


this is becoming a nightmare to me

thanks for any help

custom format of cells can have numbers with leading zeros
formatcellsnumbercustom000
change the text to numbers
by
=value(text)

If however you want them to remain text as I think you want to from the
second half of your statement
if the length of the _____1 portion is always the same (8?) so that you want
11 characters

try
=rept("0",11-len(text_cell))&text_cell

"sparks" wrote:

We have cells that are text and 1 2 3 .....110

the problem is now they want them as
001
002
099
100


can can the 1 2 3 98 99 be converted to leading 0's automatically

this wouldn't be too bad but it also has visit no's

so I have

ID VISIT
1_____1
1_____2
1_____3
1_____4
2_____1
..
..
..
99_____3
99_____4

ect


this is becoming a nightmare to me

thanks for any help

Is the underscore symbol part of the entry for that column, or have
you just put those in to make your posting clearer? If they are
included, then this formula will give you what you want:

=TEXT(LEFT(A1,FIND("_",A1)-1),"000")&MID(A1,FIND("_",A1),255)

assuming your values are in cell A1. Put this in B1 and copy down the
column by double-clicking the fill handle (the small black square in
the bottom right corner of the cursor).

You can then hide column A, or you could fix the values in column B
and then delete column A.

Hope this helps.

Pete


On Jul 16, 2:56 pm, sparks wrote:
We have cells that are text and 1 2 3 .....110

the problem is now they want them as
001
002
099
100

can can the 1 2 3 98 99 be converted to leading 0's automatically

this wouldn't be too bad but it also has visit no's

so I have

ID VISIT
1_____1
1_____2
1_____3
1_____4
2_____1
.
.
.
99_____3
99_____4

ect

this is becoming a nightmare to me

thanks for any help

Thanks big time to everyone for the help
this works fine


thanks again

Sparks


On Mon, 16 Jul 2007 08:02:08 -0700, Pete_UK
wrote:

Is the underscore symbol part of the entry for that column, or have
you just put those in to make your posting clearer? If they are
included, then this formula will give you what you want:

=TEXT(LEFT(A1,FIND("_",A1)-1),"000")&MID(A1,FIND("_",A1),255)

assuming your values are in cell A1. Put this in B1 and copy down the
column by double-clicking the fill handle (the small black square in
the bottom right corner of the cursor).

You can then hide column A, or you could fix the values in column B
and then delete column A.

Hope this helps.

Pete


On Jul 16, 2:56 pm, sparks wrote:
We have cells that are text and 1 2 3 .....110

the problem is now they want them as
001
002
099
100

can can the 1 2 3 98 99 be converted to leading 0's automatically

this wouldn't be too bad but it also has visit no's

so I have

ID VISIT
1_____1
1_____2
1_____3
1_____4
2_____1
.
.
.
99_____3
99_____4

ect

this is becoming a nightmare to me

thanks for any help

You're welcome - thanks for feeding back.

Pete

On Jul 16, 5:40 pm, sparks wrote:
Thanks big time to everyone for the help
this works fine

thanks again

Sparks

On Mon, 16 Jul 2007 08:02:08 -0700, Pete_UK
wrote:



Is the underscore symbol part of the entry for that column, or have
you just put those in to make your posting clearer? If they are
included, then this formula will give you what you want:

=TEXT(LEFT(A1,FIND("_",A1)-1),"000")&MID(A1,FIND("_",A1),255)

assuming your values are in cell A1. Put this in B1 and copy down the
column by double-clicking the fill handle (the small black square in
the bottom right corner of the cursor).

You can then hide column A, or you could fix the values in column B
and then delete column A.

Hope this helps.

Pete

On Jul 16, 2:56 pm, sparks wrote:
We have cells that are text and 1 2 3 .....110

the problem is now they want them as
001
002
099
100

can can the 1 2 3 98 99 be converted to leading 0's automatically

this wouldn't be too bad but it also has visit no's

so I have

ID VISIT
1_____1
1_____2
1_____3
1_____4
2_____1
.
.
.
99_____3
99_____4

ect

this is becoming a nightmare to me

thanks for any help- Hide quoted text -

- Show quoted text -