CODEWITHSUNDEEP
excelstringexcel-formulafilenames
Paweł Kędzielski
Paweł Kędzielski

Reputation: 33

How to change string name to specific string name?

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")

Upvotes: 0

Views: 96

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57753

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)

Upvotes: 0

Paweł Kędzielski
Paweł Kędzielski

Reputation: 33

It is solved,

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

Upvotes: 1

Related Questions