formula wanted please, I have a list of data

[jvoortman]

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

 

[Mallycat]

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

 

[jvoortman]

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!

 

[Biff]

Hi!

Use a temporary helper column and enter this formula:

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

Then convert those formulas to constants by doing:

Edit>Copy
Edit>Paste special>Values

Move the helper column and overwrite the original column.

Biff

 

[jvoortman]

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!

 

[JLatham]

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

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!

 

[jvoortman]

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

 

[jvoortman]

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

 

[jvoortman]

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

 

[Biff]

You're using Google groups!

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

Biff

 

[jvoortman]

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