Remove special characters and some string

[johnparecto]

Hello Community,

Asking your help if you can share what steps or condition (formula) what will I do to the following:

Current data:

I want to remove some data and characters.

- <CdtTrfTxInf>	- <PmtId>	<InstrId>3VNUG</InstrId>	<IntrBkSttlmAmt Ccy="GBP">98.40</IntrBkSttlmAmt>
- <CdtTrfTxInf>	- <PmtId>	<InstrId>3O7G</InstrId>	<IntrBkSttlmAmt Ccy="GBP">2052.00</IntrBkSttlmAmt>
- <CdtTrfTxInf>	- <PmtId>	<InstrId>3OWP5</InstrId>	<IntrBkSttlmAmt Ccy="GBP">94726.50</IntrBkSttlmAmt>

And I want to look like this way.

3VNUG	GBP	98.40
3O7G	GBP	2052.00
3OWP5	GBP	94726.50

[DataNath · Accepted solution]

Hey @johnparecto, is this format consistent? If so, you should be able to isolate these elements with RegEx. Here's a quick example I've put together:

Output:

Formula expressions:

If you're not going to have any other numbers/decimal points in your [Field4] then you could even simplify the RegEx massively and use:

REGEX_Replace([Field4], '[^0-9.]', '')

Hopefully this is useful! Please shout if you have any questions etc.

[johnparecto]

Thank you so much! It helps.