Search Column for ':' and ';' AND Create Additional Column for Info After ':' AND Create Additional Rows for Info After ';'

[qujeoiandlkamfpoqwm]

I have a 2D array of strings with 3 columns and an unknown number of rows. This information was extracted from a larger data set where the the now third column has to have a colon (:). Within this third column, each cell has string such as "123:Error Message." I would like to search the column to keep the text before the colon in the third column and then move the text after the colon into a fourth column. 

There's added complexity where the string in the third column can also contain multiple sets of strings where each is being divided with a semicolon (;). For example, "123:Error Message; 456:Fault Message; 789:Limit Message." The number of times this can happen varies. There can be one, two, or any number of times there are strings separated by a ';'. When there are such times where this occurs, I would like each set to be the next row in the array with the first two columns staying the same as the first set. The same principle of making a fourth column for the text after the colon needs to be done in conjunction. 

2D array example with arbitrary values for first two columns:

123    |      231313   | 123:Error Message

1312  |      2323       | 456:Error Message; 789:Fault Message

2        |      172313   |  9:Error Message; 23:Fault Message; 89:Limit Message

RESULT:

123    |      231313   | 123                |          Error Message

1312  |      2323       | 456                |          Error Message

1312  |      2323       | 789                |          Fault Message

2        |      172313   |  9                   |          Error Message 

2        |      172313   | 23                  |          Fault Message

2        |      172313   | 89                  |          Limit Message

[Bob_Schor]

Sounds simple enough.  You shouldn't even need to send us your code -- you can easily generate your own Test Data (you did, already, in the Message) and can simply test your code to see that it works.  So what do you need us for?  [No, we are not going to "do your work", whether it is school work, home work, course work, or job work, for you.  If you try and "get stuck", then send us your code ...].

Bob Schor

[daveTW]

But (at least) you explained your problem very well. That's more than most TOs do.

[tst]

So you will want to iterate through the rows of the array (for loop), handle each element separately (array palette), split a string (string palette) and build an array from the result. I have pointed you to the relevant palettes. I would suggest also right clicking the tunnel you get when you wire an array out of a loop and see what options you have there, as that would also include some useful things.

If things are still unclear to you, split them into smaller tasks and once you understand each concept, then you can put them together.

[qujeoiandlkamfpoqwm]

So far, I've indexed each element in the third column us a for loop. Then discovered the Search/Split String function to search for the colon. The substring before match will be the new third column while the match + rest of string will be the fourth column. I deleted the colon using string subset. The new array was created using build array with the original two columns, and the two new columns. 

I'm struggling implementing the second part of my objective. I guess I can use Search//Split String to search for the semicolon but I don't know how to identify if there were to be multiple semicolons. In addition extracting each separately into new rows while also keeping the first two columns values the same has been a struggle understanding. 

[altenbach]

See if this can give you some ideas.

Of course more code might be needed to deal with special cases. What if the third column is empty? What if the third column has no ":"? etc.

[altenbach]

Or even simpler by just using a concatenating tunnel:

(Again, if the third column can be empty, more code is needed.)

[qujeoiandlkamfpoqwm]

No elements in the third column will be empty or not contain the colon (:). I looked at the data set again and noticed there was something I forgot to mention. The text after the colon can contain a semicolon within parenthesis. There were multiple instances such as "123:Error Message(4;5;6)". The text after the colon "Error Message(4;5;6)" should be in the fourth column and not just the words. 

[altenbach]

---

@qujeoiandlkamfpoqwm wrote:

The text after the colon can contain a semicolon within parenthesis. There were multiple instances such as "123:Error Message(4;5;6)". The text after the colon "Error Message(4;5;6)" should be in the fourth column and not just the words. 

---

OK, now your are changing the requirements! This should have been mentioned from the beginning, of course!

Starting with my latest example, It can easily be fixed with a few trivial changes. try it! 😄

[altenbach · Accepted solution]

---

@altenbach wrote:

---

Starting with my latest example, It can easily be fixed with a few trivial changes. try it! 😄

---

For example if the message delimiter is a ";" followed by a space while the inner ";"s are not, all you need is change the delimiter to "; ", i.e. a semicolon followed by a space character.

If this does not always work, we need much better problem specifications!