Would like to extract the No value from the below comments column and will you please suggest the RegEx functionality. Sl.No. Comments1Messages 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.2Messages 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.3Messages 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.4Messages 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.5Messages 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.

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

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.

Developer

Developer Tools

Preparation

In Database

Data Investigation

In-Database

Accepted answers

Thableaus

@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,

TextColumns.yxmd

All comments

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703