How to change string name to specific string name?

Question

I want Excel (or Linux command) to change the string of values.

From:

e.g. column A

IN_EMAIL.201_101300_180403_131131_6160_5593

To:

e.g. column B

EMAIL.201_101300_0_180403_131131616_0000_5593

So:

1. Remove "IN_"
2. Add "0_" after 20th character
3. Remove "_" after 33rd character
4. Add "_000" after 37th character

I've got two formulas. How can I nest them into one?

=REPLACE(REPLACE(A4;1;3;"");18;0;"0_") 
=REPLACE(REPLACE(B4;33;1;"");36;0;"_000")

Answer 1

If you want to combine these two formulas:

=REPLACE(REPLACE(A4;1;3;"");18;0;"0_") 
=REPLACE(REPLACE(B4;33;1;"");36;0;"_000")

Just replace B4 with the first formula

=REPLACE(REPLACE(REPLACE(REPLACE(A4;1;3;"");18;0;"0_");33;1;"");36;0;"_000")

Alternatively you could use the following formula that might be more obvious:

=MID(A5;4;17) & "0_" & MID(A5;21;13) & MID(A5;35;3) & "_000" & RIGHT(A5;6)