Find and replace a specific number

[Closed]
Report
Posts
1
Registration date
Monday March 23, 2015
Status
Member
Last seen
March 23, 2015
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hi All,

I am new to this, so I hope somebody can help.

I have a code CF|0612|1-12 in cell A1.

That code is the code for an apartment block we own. Within that block are 12 apartments all made up of DW codes.

So within the CF code, will be 12 DW codes (dictated by the 1-12 at the end)

We would have:-

DW|0612|1
DW|0612|2
DW|0612|3
DW|0612|4
DW|0612|5
DW|0612|6
DW|0612|7
DW|0612|8
DW|0612|9
DW|0612|10
DW|0612|11
DW|0612|12

I need a formula that would change the CF (although I can do this with =IF(ISTEXT(A1024),REPLACE(A1024,1,2,"DW"),B1023)

I just need something to add to this that looks at the last bit (1-12) and will add each one consecutively.

I hope this makes sense and that somebody can help.

Many thanks for your time

Phill

1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Phill,

You can put an & symbol at the end of you formula to add more formula's or text/numbers.

So your formula could look like:
=IF(ISTEXT(A1024),REPLACE(A1024,1,2,"DW"),B1023) & 1

Dragging the formula down won't work unless you put the numbers 1 to 12 in a separate location and then refer to that location instead of the 1.

So if C1:C12 contains 1 to 12, then formula would be:
=IF(ISTEXT(A1024),REPLACE(A1024,1,2,"DW"),B1023) & C1

Best regards,
Trowa