selectively add sequence number to an Excel 2003 column

[jgrogan]

Hi all

I have a column in excel holding things like

abc
delimiter
def
delimiter
ghi

the 'deimiter' is literally the string 'delimiter'. I need to add a sequence number to each occurrence of the word 'delimiter' in the column, giving something like:

abc
delimiter001
def
delimiter002
ghi
...
delimiternnn

Any ideas?

Thanks
JG

 

[RivetHed]

Will the delimiter always appear every other row?

 

[jgrogan]

Yes. I have not seen any examples that suggest otherwise.

Thx.

 

[RivetHed]

Then I'd try something like this in column B to convert column A into the format you want:

=if(left(A3,9)="delimiter","delimiter"&TEXT(RIGHT(B1,3)+1,"000"),A3)

You'll need to start off on the third row of the sheet otherwise it'll get confused trying to check 2 rows up.

 

[jgrogan]

Thanks RivetHed - works a treat!

JG

 

[AnotherHiggins]

Just another idea....

To get the number, rather than adding from above, you could use the row to figure it out. If every other line is DELIMITER and the first DELIMITER is on row 2, then

=IF(MOD(ROW(),2)=0,A1&TEXT(ROW()/2,"000"),A1)

or just plug the row part into RivetHed's formula:
=if(left(A1,9)="delimiter","delimiter"&TEXT(ROW()/2,"000"),A1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.