 # Help needed replacing parts of numbers with other numbers

• Hello there

I am a total excel noob. I don't know if this is a macro or formula so if in the wrong group sorry.

I have column A with numbers like this and there are over 1000 of them
004 0 10 13
004 0 10 14
004 0 10 15
012 0 03 00
012 0 03 01
012 0 03 02
012 0 03 03
I would like to be able change the first set of numbers 004 to 056 and the 012 to 048
Basically i have the following to change
004 to 056
012 to 048
020 to 052
024 to 044
028 to 040

I also need to change the next digit 0 to a 1 if the set of digits is 08 to 15 and then if the third set is 08 to 15 if would need to be changed to
08 to 00
09 to 01
10 to 02
11 to 03
12 to 04
13 to 05
14 to 06
15 to 07

Here are a few examples
004 0 01 01 needs to be changed to 056 0 01 01
004 0 08 12 needs to be changed to 056 1 00 12

Can this all be done with one formula or do I have to do this in stages or can it not even be done? I would like the original number to be in column A and the new number in column B.

Any help would be greatly appreciated.

• I think you could do this in an IF formula, using a mixture of LEFT, RIGHT and CONCATENATE.

For example, the below will replace the first three digits for you. You would have to create something similar to replace the other digits using MID instead of LEFT

=IF(LEFT(\$A1,3)="004",CONCATENATE("056",RIGHT(\$A1,8)),IF(LEFT(\$A1,3)="012",CONCATENATE("048",RIGHT(\$A1,8)),IF(LEFT(\$A1,3)="020",CONCATENATE("052",RIGHT(\$A1,8)),IF(LEFT(\$A1,3)="024",CONCATENATE("044",RIGHT(\$A1,8)),IF(LEFT(\$A1,3)="028",CONCATENATE("040",RIGHT(\$A1,8)),"")))))

Someone better than I will likely have a cleaner answer, and probably get both in the same formula. I am by no means fluent in this but hopefully it helps a bit

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!