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!