Find and replace a specific number

Closed
phill4356 Posts 1 Registration date Monday March 23, 2015 Status Member Last seen March 23, 2015 - Mar 23, 2015 at 05:58 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 24, 2015 at 12:52 PM
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.

Phill

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 24, 2015 at 12:52 PM
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