Skip to main content

Splitting a string field into separate fields using a delimiter (e.g. comma, space, pipe, semicolon etc.)

Hello Everyone
 
I hope all is well.
 
May you please help if you have a solution around this? I know this was a covered on the below link, however, using the IDEA functions (@Left,@Right,@Split, @Simplsplit, @Mid, @JustNumbers) described, do not work for my currect situation. There are a lot of characters and segments, so trying to use IDEA functions is not effecient:
https://www.ideascripting.com/forum/text-columns-idea
 
Here is my current situation:

From the abave i need to segment each part in its own column (see attachment if the above screenshot is not showing). SS -> column 1, 0.875% -> column 2, (0-200m) -> column 3, 0.85% -> column 4 and so on. Now this is achived quickly in Excel or Power BI, by using a space as a delimiter. I have about 16 000 lines of different numbers and lengnths, delimited by space as shown above. Is there a way to acheive this IDEA without exporting my data to Excel to separate the field into multiple fields or do the separation before import into IDEA? 
 
Believe me I have tried the IDEA functions and nothing provided the results in the manner that I needed, after spending a lot of time, other data tools have a quick solution around this.
 
Thank you.
Smiso.

Brian Element Mon, 06/21/2021 - 20:41

Hi Simso, is that what you are looking for?  If so I used the @simplesplit to complete each of the fields.  I have attached my imd file so you can see the equations and try it out.  The imd file is IDEA 11 so if you can't read it let me know and I will post my equations.

Smiso Tue, 06/22/2021 - 07:07

Hi Brian
 
Thank you very much man.
 
I am embarrassed, I didnt know that simplesplit can work so well in this situation. I actually tried other function except for simplesplit.
 
Regards.
Smiso.

Brian Element Tue, 06/22/2021 - 07:45

In reply to by Smiso

Hi Smiso,

No worries, it is hard keeping track of all the different functions available and what works best in which scenario.

Good luck with your project.