formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

the easiest way I can think of is to do numbers 1-9 by hand, numbers 10
- 99 with text to columns fixed width then numbers 100 - 999 with a
different text to columns fixed width etc.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=557529

I would agree with you except that I was stupid enough to do a sort
whuile playing around with it. So now 2 comes after 19 but before 20
and then 21-29, the 200, 201 etc. Yeah I goofed up!

Mallycat wrote:
the easiest way I can think of is to do numbers 1-9 by hand, numbers 10
- 99 with text to columns fixed width then numbers 100 - 999 with a
different text to columns fixed width etc.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=557529

Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

thnks Biff works ...sort of. When I past it in B1 it does what I want
it to but it makes the cell twice as tall thereby taking up B2 also.
This stops me from doing a fill down to do the whole list. Any ideas?
Thanks eh!


Biff wrote:
Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

I'm a little unsure of what you are needing to do here. Get things back into
the original sequence? If so, you can try this (assuming column B is
available, otherwise, use another)

in B1
=REPT("0",3-(FIND(" ",A33)-1)) & A33
and drag down to the end of your list
1 some text
24 some more text
399 even more text

will appear as
001 some text
024 some more text
399 even more text

Then you can copy all of that and use Edit | Paste Special with the [Values]
option checked to turn that into 'real text' instead of formula results and
sort and get it back into the original sequence?

Now if I missed that target, here are formulas to actually split the entries
at the space after the number:
B1
=LEFT(A33,FIND(" ",A33)-1)
C1
=RIGHT(A33,LEN(A33)-FIND(" ",A33))


" wrote:

I would agree with you except that I was stupid enough to do a sort
whuile playing around with it. So now 2 comes after 19 but before 20
and then 21-29, the 200, 201 etc. Yeah I goofed up!

Mallycat wrote:
the easiest way I can think of is to do numbers 1-9 by hand, numbers 10
- 99 with text to columns fixed width then numbers 100 - 999 with a
different text to columns fixed width etc.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=557529

sorry Biff, I messed up. I tried it again but instead of pasting the
formula in cell b1, I selected cell b1 andthen pasted in the white
address box on the top. Not sure what the difference is, but the cell
didn' bloat, and now I can drag down and copy.THANK!!!!


wrote:
thnks Biff works ...sort of. When I past it in B1 it does what I want
it to but it makes the cell twice as tall thereby taking up B2 also.
This stops me from doing a fill down to do the whole list. Any ideas?
Thanks eh!


Biff wrote:
Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

sorry Biff, I messed up. I tried it again but instead of pasting the
formula in cell b1, I selected cell b1 andthen pasted in the white
address box on the top. Not sure what the difference is, but the cell
didn' bloat, and now I can drag down and copy.THANK!!!!


wrote:
thnks Biff works ...sort of. When I past it in B1 it does what I want
it to but it makes the cell twice as tall thereby taking up B2 also.
This stops me from doing a fill down to do the whole list. Any ideas?
Thanks eh!


Biff wrote:
Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

sorry Biff, I messed up. I tried it again but instead of pasting the
formula in cell b1, I selected cell b1 andthen pasted in the white
address box on the top. Not sure what the difference is, but the cell
didn' bloat, and now I can drag down and copy.THANK!!!!


wrote:
thnks Biff works ...sort of. When I past it in B1 it does what I want
it to but it makes the cell twice as tall thereby taking up B2 also.
This stops me from doing a fill down to do the whole list. Any ideas?
Thanks eh!


Biff wrote:
Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

You're using Google groups!

Sometimes when I copy/paste stuff from Google Groups it brings with it the
weird formatting.

Biff

wrote in message
oups.com...
sorry Biff, I messed up. I tried it again but instead of pasting the
formula in cell b1, I selected cell b1 andthen pasted in the white
address box on the top. Not sure what the difference is, but the cell
didn' bloat, and now I can drag down and copy.THANK!!!!


wrote:
thnks Biff works ...sort of. When I past it in B1 it does what I want
it to but it makes the cell twice as tall thereby taking up B2 also.
This stops me from doing a fill down to do the whole list. Any ideas?
Thanks eh!


Biff wrote:
Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have
this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them
manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99
have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!

sorry Biff, I messed up. I tried it again but instead of pasting the
formula in cell b1, I selected cell b1 andthen pasted in the white
address box on the top. Not sure what the difference is, but the cell
didn' bloat, and now I can drag down and copy.THANK!!!!


wrote:
thnks Biff works ...sort of. When I past it in B1 it does what I want
it to but it makes the cell twice as tall thereby taking up B2 also.
This stops me from doing a fill down to do the whole list. Any ideas?
Thanks eh!


Biff wrote:
Hi!

Use a temporary helper column and enter this formula:

=MID(A1,FIND(" ",A1)+1,255)

Then convert those formulas to constants by doing:

EditCopy
EditPaste specialValues

Move the helper column and overwrite the original column.

Biff

wrote in message
ups.com...
formula wanted please, I have a list of data going down column "A"
only. When it was typed out they numbered each entry (if they would
have just done the numbering in a seperate column I wouldn't have this
question).

it looks like this;

1 assorted text
2 more assorted text
3 more assorted text

there are over 600 entries so I really don't want to do them manually.
I tried "text to column" and using the "fixed" choice but it chops
stuff up because some numbers like 1-9 have one digit while 10-99 have
2 digits and the hundreds have 3.

There is probably an easy way ......but I need your help...PLEASE!!