Would like to extract the No value from the below comments column

[viswamtulasi]

Would like to extract the No value from the below comments column and will you please suggest the RegEx functionality.

Sl.No.	Comments
1	Messages are used by youth and adults for personal, family, business and social purposes. Governmental and non-governmental organizations use text messaging for communication between colleagues. In No: 889911, the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing.
2	Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing.
3	Messages are used by youth and adults for personal, family, business and social purposes. No: 222 Governmental,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing.
4	Messages are used by youth and adults for personal, family, business and social purposes. Governmental,and non-governmental organizations use text messaging for communication between No: 5987 colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing.
5	Messages are used by youth and adults for personal, family, business and social purposes. Governmental,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened No: 11234 earlier with emailing.

[Thableaus]

Hey @viswamtulasi 

This should work:

REGEX_Replace([ Comments], ".*?(No:\s\d+).*", "$1")

Cheers,

[Thableaus]

@viswamtulasi 

It worked fine here.

Cheers,

[viswamtulasi]

Yeah it works and Thanks Thableaus.

Also, I’ve different scenario as below and would like to find the blocked values from the comment column.  

Sl.No.	Comments
1	Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 10/20/2017 Student No.: AB1089 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Analytics Course ID: 141992 Col 1 #: 35280 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 83476483
2	Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 11/22/2018 Student No.: Z1089 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Data 22 Course ID: 141992 Col 1 #: DS3843834343 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 48563
3	Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 01/25/2018 Student No.: Za13439 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: .Net Course ID: 141992 Col 1 #: 2838473 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 4973
4	Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 11/25/2017 Student No.: Z108222 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Sun Sol Course ID: 141992 Col 1 #: 7565234 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 8347343234
5	Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 05/15/2018 Student No.: BB31089 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Java Course ID: 141992 Col 1 #: 12 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 38738392983

Solution to find: from the Comments need to extract the data values as below –

Date Completion	Student No	Area Name	Col 1 #	Other Code
10/20/2017	AB1089	Analytics	35280	83476483
11/22/2018	Z1089	Data 22	DS3843834343	48563
01/25/2018	Za13439	.Net	2838473	4973
11/25/2017	Z108222	Sun Sol	7565234	8347343234
05/15/2018	BB31089	Java	12	38738392983

[Thableaus · Accepted solution]

@viswamtulasi 

Just a tip of how the community works: everytime you have a new question, please raise a new topic. It keeps the community organized and easier for people to look for solutions when they need to.

It's a best practice to close a topic and mark as solved and raise a new topic when you have a new question. Also, you get more visibility from other peers that can help you out.

Anyway, here's the solution for your problem:

- Use Text to Columns in the Comments column with a "\n" delimiter and option Split to Rows

- Use another Text to Columns in the Comments column with a ":" delimiter and option Split to Columns (2 columns)

- Cross-Tab tool grouping by Sl No., with Comments1 as New Column Headers and Comments2 as Values for New Columns

- Data Cleansing Tool to get rid of trailing and leading whitespaces

- Select tool to keep only the columns you need and rename them due to underscores created by Cross-Tab tool.

Attached workflow on version 2018.4

Cheers,