Split a text at every "AND" using text to columns

[arlok2005]

My requirement is as below:-

1.I want to split a text at every AND and put it into new columns.

2.But the number of ANDs might vary.For example:

One data point in the given column would have Name AND School AND City AND Country.and i would want 4 columns created for Name,School,City,Country.

But another data point might have Name AND School AND Country.Here i would want the same columns but with Blank in the city column.

Sample data:-

ContatinatedStringColumn

1.Name contains('Tom') AND School contains ('ABC High School') AND City Contains('Vancouver','Toronto') AND Country Contains ('Canada')

2.Name contains('Adam') AND School contains ('ABC High School') AND Country Contains ('Canada')

How do i do this?Any help would be appreciated.

[T_Willins]

Hi @arlok2005,

The key is not the parsing, but how to identify which data point belongs in each column.  Think of teaching a person how to do this if that person had no context of city or country.  Absent a key (i.e. - Text Box with list of Countries) or training (machine learning), it becomes almost impossible for that person to be completely correct.  Even then there is a possibility for error where a city is named after a country (Singapore, MI).  Are there any other identifiers in the data?  Sample data may help.

[arlok2005]

Ive amended the question to have sample data.

Am pasting the sample data as a response to you aswell:

Sample data:-

ContatinatedStringColumn

1.Name contains('Tom') AND School contains ('ABC High School') AND City Contains('Vancouver','Toronto') AND Country Contains ('Canada')

2.Name contains('Adam') AND School contains ('ABC High School') AND Country Contains ('Canada')

[AbhilashR]

Hi @arlok2005, one way to build this solution could be to use Text-to-Column in conjunction with Tile tool to compute your output. I have included a sample workflow that you could build upon.

Let us know if this isn't what you are looking for. 

[wwatson]

I had a go. The trick will be knowing which value is missing.

[wwatson]

Another Version

[T_Willins]

Another option that matches your data

[brendafos]

Did the whole thing with as single RegEx expression.