How Do I split a column into 4 columns

[franksky]

The following string is in 1 field.

Share A (All Sessions) CONS 32@19300 098JE:588814~6557 Converted at 0.7362389047244189003

How can I split into 4 columns?

Share A (All Sessions)

32

19300

0.7362389047244189003

CONS and Converted at are the key words repeated in all rows.

Thanks

[BenMoss]

And @?

I would consider taking an easy route (you could go regex but I'm going to go quick and simple).

replace(replace(replace([field],"CONS","|"),"Converted at","|"),"@","|")

Then use a text to columns tool with a pipe delimiter.

Ben

[danrh · Accepted solution]

In the RegEx tool, use the following as your expression and set your Output method to Parse:

(.*) CONS (\d+)@(\d+).*Converted at ([\d\.]+)

Below the expression you can re-name the output fields and change data types.

[franksky]

Thanks, Danrh,

It worked perfectly. Thanks

[franksky]

Thanks Ben,

I did similar replace formular and tried to split. But '098JE:588814~6557' is a random bit string in each record which we need to ignore.

Perhaps I didn't used text to column correctly. But thanks for the helps.