Extract Data from String

[Shubham_Akole]

Hi,
I want to extract data from 1 string which is in the excel Sheet (B6 Cell)…
Total Present - 26 Total Absent - 0 Total Leave Taken - 0 Total Weekly Off Present - 2 Total Duration - 252:00 Total T.Duration - 252:00 Total Over Time - 00:00 Total LateBy - 00:00 (Hrs.) Total EarlyBy - 00:00 (Hrs.) Total Regular OT - 00:00 (Hrs.)

This is string…I want to extract Total Present,Absent, Total Duration,etc…
and Store in the Excel Sheet…
Sheet Like this…
Present | Absent | T.Duration
26 | 0 | 252:00

[schwarzp (Patrick Schwarz)]

Hi, you should go with Regex. You can use the “matches” activity in studio.

For Total Present the Regex pattern could be like that:

[Shubham_Akole]

What is expression for Total duration

[hasib08 (Motiwala Hasib)]

Use this regex to extract the information

regex101: build, test, and debug regex

Group 1 = Total present
Group 2 = Total Absent
Group 3 = Total Duration

[Shubham_Akole]

When I use this…And for use message Box for value it show…

System.Linq.Enumerable+d__94`1[System.Text.RegularExpressions.Match]

[Shubham_Akole]

This is not work when total Present-23.5

[mzahid (Zahid)]

@Shubham_Akole

Total Present - 26 Total Absent - 0 Total Leave Taken - 0 Total Weekly Off Present - 2 Total Duration - 252:00 Total T.Duration - 252:00 Total Over Time - 00:00 Total LateBy - 00:00 (Hrs.) Total EarlyBy - 00:00 (Hrs.) Total Regular OT - 00:00 (Hrs.)

Regex for Total Duration

(?<=Total Duration)*\d+:\d+(?=\sTotal T.Duration)

Regex for Total present

(?<=Total present)*\d+(?=\sTotal Absent)

If Total present is 23.5 if it is decimal value please use below pattern, This should work for both decimal as well as non decimal integer

Regex
(?<=Total present)*\d.+(?=\sTotal Absent)

Regex for Total Absent

(?<=Total Absent)*\d+(?=\sTotal Leave)