formula to add amend a number/text

[cat3appr]

Hi all,

Please see the attached photo and file , you see the colum D ( from D3116)
I need to make the following cells as follow : from cell D3116 the content has to chage from P0997 it has to become P0998, and the next cell D3117 has to become P0997, and so on,
basically from D3116 the naming in that cell has to start to increase. But not being a number, i'm not sure how to do this

many thanks!

[FDibbins]

1. your references dont match your file? (you probably deleted a bunch of rows for your sample, but did not adjust the ranges in your question?)

2. you could probably do this with Find/Replace. A formula will not do this for you (directly), you will need to use helper columns, then copy/paste values back over the original data

[oeldere]

d6 ="P"&TEXT(1004-ROW(),"0000") and drag down.

[JohnTopley]

What is the underlying formula?

Values decrease by 1 as you move down the column skipping when column B is blank(?).

Row 16 (D16) has this "P0989 RW" - how is this handled?

[JohnTopley]

@oeldere,
Your solution doesn't account for blank entries. See my post.

[oeldere]

@oeldere,
Your solution doesn't account for blank entries. See my post.

The OP has to answer, if that is a issue (or not).

As often a more detailed example with the expected results is very usefull for getting the best anwer.

[cat3appr]

d6 ="P"&TEXT(1004-ROW(),"0000") and drag down.

What is the underlying formula?

Values decrease by 1 as you move down the column skipping when column B is blank(?).

Row 16 (D16) has this "P0989 RW" - how is this handled?

in fact the formula doesn't take into account of the RW cells.... sorry but so far the issue it hasn't been solved

[JohnTopley]

What about the blank entries?

[oeldere]

Post an small excel file, and add in that file the expected results.