String separation

[#1]

Hello,

I have a text string with three information "MLB B/C/AU491_2022"

Platform: MLB B/C

Program: AU491

SOP: 2022

Separators are '/' and '_'

I have tried to use TextBetween, but as you can see in some strings the separator / is uses twice.

How can I script it to get three separate strings?

more examples of the string:

MRA MID-SIZE/W206_2021

MLB B/C/AU491_2022

GA-K/480B_2019

Many thanks!

jens

[Chanty4u]

what is your expected output?

[Chanty4u]

Hi,

check the posibility

a:

load * Inline [

text

MRA MID-SIZE/W206_2021

MLB B/C/AU491_2022

GA-K/480B_2019

];

  Result:

LOAD *,

PurgeChar(text,'-/_') as total,

KeepChar(text,'ABCDEFGHIJKLMNOPQRSTUVWXYX') AS string,

  KeepChar(text,'0123456789') AS Number

Resident a;

[tresesco]

To handle that multiple separator you could use third parameter of textbetween(), like:

=TextBetween(String, '/', '_', SubStringCount(String,'/'))

[jaumecf23]

Maybe first you can use the function Replace(). To replace when a delimiter will be '_' to '/'. Using this trick you will have always a unique delimiter. Replace(Field,'_','/')

After that there is a function called SubField(), that need three parameters. This function splits the field provided using the delimiter and then the defined position is returned:

SubField(Field, Delimiter,indexPosition)

You can use something like this:

Platform: SubField(Field, '/',1)

Program: SubField(Field, '/',2)

SOP: SubField(Field, '/',3)

[effinty2112]

Hi Jens,

If vStr is the input then:

Platform : left('$(vStr)',len('$(vStr)')- len(SubField( '$(vStr)','/',-1))-1)

Program : subfield(SubField( '$(vStr)','/',-1),'_',1)

SOP :subfield(SubField( '$(vStr)','/',-1),'_',-1)

Input	Platform	Program	SOP
GA-K/480B_2019	GA-K	480B	2019
MRA MID-SIZE/W206_2021	MRA MID-SIZE	W206	2021
MLB B/C/AU491_2022	MLB B/C	AU491	2022

Cheers

Andrew

[#7]

Many thanks to all of you.

I will check the different solutions.

Regards

Jens

[jmvilaplanap]

Hi,

If the field is a concatenation of another fields made by you or anyone can made changes in the data source, I suggest change the separation char, because this char can be used inside a field.

For example:

• "MLB B/C#AU491#2022"

And use the function Subfield to get all this three fields

• Field1 = subfield(Field, '#', 1)
• Field2 = subfield(Field, '#', 2)
• Field3 = subfield(Field, '#', 3)