Substitute Text Between Two Delimiter

Amit_Jain1
Participant III

Hello Friends,
I want to SUBSTITUTE Text Column Between Two Delimiter to New Text

Eg. From
HM Nat. 11 X 36 X 230@150@121

To
HM Nat. 11 X 230@150@121

I want To Replace " X 36 X "
With " X "

Any Idea How to Achieve This Desired Result from Expression

Solved Solved
0 9 560
1 ACCEPTED SOLUTION

hI @Amit_Jain,

Please use an expression something like below

CONCATENATE (INDEX(SPLIT(โ€œHM Nat. 11 X 36 X 230@150@121โ€,โ€œXโ€),1),โ€œXโ€, INDEX(SPLIT(โ€œHM Nat. 11 X 36 X 230@150@121โ€,โ€œXโ€),3))

Or let us say your original text is available in column [Original Text] and let us call delimeter character as โ€œdelimeterโ€ (โ€œXโ€) in your case then the expression will be something like

CONCATENATE(INDEX(SPLIT([Origina Text],โ€œdelimeterโ€),1),โ€œdelimeterโ€, INDEX(SPLIT([Original Text],โ€œdelimeterโ€),3))

Below image shows the expression tested in app editor

View solution in original post

9 REPLIES 9

hI @Amit_Jain,

Please use an expression something like below

CONCATENATE (INDEX(SPLIT(โ€œHM Nat. 11 X 36 X 230@150@121โ€,โ€œXโ€),1),โ€œXโ€, INDEX(SPLIT(โ€œHM Nat. 11 X 36 X 230@150@121โ€,โ€œXโ€),3))

Or let us say your original text is available in column [Original Text] and let us call delimeter character as โ€œdelimeterโ€ (โ€œXโ€) in your case then the expression will be something like

CONCATENATE(INDEX(SPLIT([Origina Text],โ€œdelimeterโ€),1),โ€œdelimeterโ€, INDEX(SPLIT([Original Text],โ€œdelimeterโ€),3))

Below image shows the expression tested in app editor

Thanks @Suvrutt_Gurjar For Your Reply
Your Suggestion Worked

Thank you @Amit_Jain for your update. Good to know the expression was useful to you.

Not sure about your values in generally, but if the string is always like โ€œX 36 Xโ€, you could useโ€ฆ
SUBSTITUTE([ColumnName],โ€œX 36 Xโ€,โ€œXโ€)

No Sir
Its Variable

Every Time Its Different Value

But Right Left Delimiter are Same

Hi @Aleksi,

You are indeed master. Your expressions are always most compact.

Hi @Suvrutt_Gurjar
@Aleksi is a true ALIEN

Hi @LeventK,

I totally agree. He always has the most compact solutions that we simply fail to think through.

Of course you are also a techie with expertize in many software technologies.

We keep getting insights on a daily basis from all in this great community.

Hi boys, 

@Aleksi  ,Is the smart boy. I hope one day I can understand all the things you write sometimes, for me it seems badwords but very badwords. ๐Ÿ˜‚

My God, what's is that:๐Ÿ™„

CONCATENATE(INDEX(SPLIT([Original Text],โ€œdelimeterโ€),1),โ€œdelimeterโ€, INDEX(SPLIT([Original Text],โ€œdelimeterโ€),3))

I understand until Split - SPLIT([Original Text],โ€œXโ€)

Learning INDEX.... aha, understood, you use it because you need to say to the app that you want after SPLIT the 1st option and then add the new with the 3th. Interesting. 

 

Thanks boys, now it seems not badwords, only new ideas. I try to find a solution for me. 

Sara 

 

Top Labels in this Space