I'm looking for a regex expression pattern for the following data. I need first column and last column information from the table.
e.g. Annual Leave 12.5 Uncertified Sick Leave 6.0 48 mths Sick Leave Full Pay 148.5 48 mths Sick Leave Half Pay 195.0
Is it possible? Thanks.
Solved! Go to Solution.
Yes, it certainly is possible
The solution all depends on how your data is structured
If the columns of data are separated by spaces, then a simple substring would enable you to pull out the data at the appropriate column divisions
If there are tab separators, then you would be able to split the row on the tab into a collection and pull out the relevent indexes.
If you really must use a regex, then you would need the start and end string anchors, but as your 'Type' field has varying numbers of spaces, that would be much trickier.
It looks to me as if there is a structure - of sorts
The hyphens underlining the column headers look as if they delimit the field sizes, therefore Id split the data into rows by using a collection operation to split on a line end
Then delete the first 3 entries to remove the headers (note, when you delete the first entry, the 2nd entry now becomes the new first one)
Then loop through the lines using a for each and use build strings to get substring of the variable, 1,27 for the Type and substring of the variable, 69,12 for the Remaining (I may have miscounted - trying it out will tell you if it is correct)