resequence with a prefix

[Guest]

excel 2007: have 1 col with many rows, each cell has a prefix prt:000 , need
to sequence all row in that col as prt:001,prt:002...and so on... any ideas
how?

TIA

 

[Bob I]

do you already have prt:001 , prt:002 ,prt:003 and you just need to
order them? If so just click the select sheet box in the upper left and
then Sort Ascending.

 

[Tim879]

Try this formula:

=LEFT(A1,4)&TEXT(MID(A1,5,1000)+1,"000")

the Left (a1,4) assumes that you use the 4 character string "prt:". If
you use a longer / shorter string, just adjust the left / mid
functions accordingly.

 

[Guest]

you mean they all have prt:000, and you need them to be 001,002.etc?
in a blank row, enter prt:001, then click on that cell. Notice the "fill
handle" a black square in the lower right hand corner of the cell. click and
hold it and drag down, it will make prt:001, prt:002, etc as you go down.
Now, in another cell, concatenate that cell with the last part of the
existing cells-
=concatenate(b1,right(a1,len(a1)-7))
or something like that

 

[Ragdyer]

Enter
prt:001
in the first cell, say A1, then enter
prt:002 in the next cell down.

Select *both* cells,
and click on the fill handle of that 2 cell selection, and simply drag down
to copy as needed.

XL will copy and increment the data *automatically*.

 

[Guest]

amazing...thanks to all. it works. I was breaking my hand trying ,left arrow
then number....amazed me that a mouse combination can do so much when working