Can you increment text in a substitute text formula

Hi All

I’m hoping someone out there has an answer to the following for me. I’m still in a novice learning curve on formulas / macros etc. and have looked for some time on google for fixes for this without luck.

Issue:
I have a spreadsheet where 1 x row equals 1 x transaction
Cell R10 in the first row (R11 second row etc.) uses a Substitute Test formula to add two ‘spaces’ to the Receipt No located in cell C10 i.e.
=SUBSTITUTE(C10,"BB15001","BB15001 ")
This then becomes the first of four parts to a File Name string created in cell W10 where the spaces are necessary to make the file name user friendly and easily readable as in the W10 example:
BB15001 John Smith JO 60.5

However; if I drag R10 to R11 and view the formula I get =SUBSTITUTE(C11,”BB15001”,”BB15001 “) whereas I want =SUBSTITUTE(C12,"BB15002","BB15002 ")
That is, the cell reference changes but not the Old_text or New_text resulting in the loss of the spaces which makes the File Name less readable. If I change the text manually it works as it should but as this spreadsheet will have over 300 rows I would like the text to increment as well when dragging R10.

Can someone please help either with the existing formula and/or alternatives resolutions?

Cheers
Brett

Hello

Can You Please attach a sample file to help you better

Regards,
Jaya

Hi Zbor

Thanks very much Zbor that works a treat, much appreciated!!

Cheers
Brett

Thanks guys for all your help its up and working

Cheers
Brett