RegEx Parse into 2 columns

[ritika27]

Hi,

I have a column in my data which looks like below:

Column A

I have to go

I need coffee

I am not feeling well

I should have not said that

He is good

His phone is ringing so loud for the past hour

I need to split it into 2 columns as follows:

Column A	Column B	Column C
I have to go	I have to	go
I need coffee	I need	coffee
I am not feeling well	I am not feeling	well
I should have not said that	I should have not said	that
He is good	He is	good
His phone is ringing so loud for the past hour	His phone is ringing so loud for the past	hour

Essentially, the last word from each record needs to be split into a new column. Can anyone please suggest? Thanks.

[DavidP · Accepted solution]

Regex parse (.*)(\<\w+\>)

[ritika27]

Works well, thanks a lot!

[ritika27]

Hi,

I have two related queries:

1. I have a column in my data which looks like below:

Column A

I have to go

I need coffee

I am not feeling well

I should have not said that

He is good

His phone is ringing so loud for the past hour

I need to split it into 2 columns as follows:

Column A	Column B	Column C
I have to go	I	have to go
I need coffee	I	need coffee
I am not feeling well	I	am not feeling well
I should have not said that	I	should have not said that
He is good	He	is good
His phone is ringing so loud for the past hour	His	phone is ringing so loud for the past hour

Essentially, the first word from each record needs to be split into a new column. Can anyone please suggest? Thanks.

2. I have a column as follows:

Date

3/18/20 1:00:00 AM

5/15/20 1:00:00 AM

11/12/19 1:00:00 AM

4/1/20 1:00:00 AM

This is currently in 'Custom' format in Excel and I want to remove the timestamps and get a 'Date' format column as:

Date

2020-03-18

2020-05-15

2019-11-12

2020-04-01

Thanks!

[Jonathan-Sherman · Accepted solution]

Hi @ritika27,

In order to solve your first query:

(\<\w+\>)(.*)

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Jonathan-Sherman]

Hi @ritika27,

And the second query could be solved by using the DateTimeParse() function and storing the column as a date data type:

DateTimeParse([Date],'%m/%d/%y')

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[ritika27]

Thanks, Jonathan. The first one worked perfectly. However for the second one, I am getting all Null values. Here is a screenshot:

Not sure why!

[ritika27]

attaching the full snapshot

[Jonathan-Sherman]

Hi @ritika27,

could you paste the first two values of your input column so I could check?

Regards,

Jonathan

[ritika27]

Here you go

Actual Start Date

3/18/20 1:00:00 AM

5/15/20 1:00:00 AM