Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to remove a certain number of characters from the beginning and end of string. What would be the best option. I have tried using LEFT and MID which does one at a time ( right or left).
In the examples, would like to remove 127 characters from the left and 21 from the right at once
<?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><Description language-id="en_US">Admin Test</Description></root>
<?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><Description language-id="en_US">Allows Access to the tool</Description></root>
Do you want to extract from this: <?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><Description language-id="en_US">Admin Test</Description></root>
What is needed to be extracted?
I need to remove the xml part and extract the text only. In the example, I would want to keep “Admin Test” only and remove the rest. There are several records in this format. So what I use should be generic.
So in here you want output to be like below?
Admin Test
Allows Access to the tool
if yes, then i would use below things.
1. Textbetween
=textbetween(YOUR FIELD,
'<?xml version='&chr(39)&1.0&chr(39)&' encoding='&chr(39)&'UTF-8'&chr(39)&'?><root available-locales="en_US" default-locale="en_US"><Description language-id="en_US">',
'</Description></root>')
2. Left & Right along with Text between
=Textbetween(YOUR FIELD, left(YOUR FIELD,127), Right(YOUR FIELD,21))
Thank you so much. I was able to get the desired result.
Bindu
I'm glad that worked. If you got what you are looking for and if this answer was correct please Mark this as Correct Answer. So that others can easily find this.
Thanks,
Phani