Parse data into particular column

[Yesha]

Hi,

I am trying to parse data into particular column. 

Example: In column A  there are 3 values - date, province and name, theses needs to be moved to 3 respective column along with other data.

 Current Data Format 

Date/Province/Name	Phone Number	Company	Currency	Amount
6/20/2020
Ontario
XYX	1324324	ds	CAD	435
FDS	1324324	df	USD	4543
DKFS	1324324	d	CAD	243
DFKJDG	1324324	vcx	USD	2345
FDGKJFD	1324324	cvx	CAD	654
4/20/2020
Quebec
DKJ	1324324	sf	CAD	57
DSJD	1324324	dsf	CAD	676
JHVF	1324324	sdf	CAD	8
KJFD	1324324	ad	CAD	76

Required Format

Name	Date	Province	Phone Number	Company	Currency	Amount
XYX	6/20/2020	Ontario	1324324	ds	CAD	435
FDS	6/20/2020	Ontario	1324324	df	USD	4543
DKFS	6/20/2020	Ontario	1324324	d	CAD	243
DFKJDG	6/20/2020	Ontario	1324324	vcx	USD	2345
FDGKJFD	6/20/2020	Ontario	1324324	cvx	CAD	654
DKJ	4/20/2020	Quebec	1324324	sf	CAD	57
DSJD	4/20/2020	Quebec	1324324	dsf	CAD	676
JHVF	4/20/2020	Quebec	1324324	sdf	CAD	8
KJFD	4/20/2020	Quebec	1324324	ad	CAD	76

I really appreciate any help. Thank you!

[jacob_kahn · Accepted solution]

@Yesha  This is a great challenge! 

Please see my attached workflow!

[Yesha]

@jacob_kahn Thank you so much!! It is great to learn reg ex and multi row tool.

[jacob_kahn]

My pleasure! My favorite part of learning new things in different tools is being able to share the love and the knowledge and appreciation. Once you get into RegEx, you never go back....lol.

Sincerely and be safe,

J

[Yesha]

@jacob_kahn - Cna you please help to understand what this RegEx do:

(^\u\l.*)

I understood about separating date .

Thank you!

[jacob_kahn]

@Yesha 

Yes! I will explain, and please let me know if the thought process will mess you up.

I only saw three different types of options in the first data column: A Date, a City, and some value of Upper Case letters.

Therefore, in the formula tool, I took the 2 left characters and said that I know the city will first have an upper case character, and the second character should be lower case. If this is the scenario I know I'm dealing with a city and not a date or some value with just Capital characters. 

Therefore, basically the RegEx reads 

( - open the field you want to parse

^ - I'm starting at the beginning of the line

\u - confirm that the first character is upper case

\l - confirm that the second character is lower case

) - close the parsed line

The value -1 means that we are dealing with an upper case character followed by a lower case character, while the value 0 means that it is not the case.

Is that more clear for you?

Please like and accept if this answers your question and feel free to continue to ask away 🙂

J

[jacob_kahn · Accepted solution]

As we discussed. Please see the second work stream.