Using RegEx to format text for import
I have this text
With lines like these
820 Review Conputer Process Annually No 2016-06-02 2019-05-31
1064 Review customer documentation Annually No 2016-07-25 2019-07-25
1282 Monitor and Clean up the Mailbox Weekly No 2015-09-21 2020-05-12
1644 Generic paragraph for monthly report Monthly Yes 2015-10-19 2017-04-13
I want to keep the text how ever I need to set a delimiter between each field.
I created a regex to Identify the fields. However I cannot seem to figure out how to use it to “replace” the correct spaces to ; as field seperator.
Sorry for the portato code…
I need to get the above text to look like this
820;Review Conputer Process;Annually;No;2016-06-02;2019-05-31
1064;Review customer documentation;Annually;No;2016-07-25;2019-07-25
1282;Monitor and Clean up the Mailbox;Weekly;No;2015-09-21;2020-05-12
1644;Generic paragraph for monthly report;Monthly;Yes;2015-10-19;2017-04-13
the first segment can be either 3 og 4 digits. There is no lenght on the second, the third can be Weekly,Monthly,Semi-annually,Quarterly,Annually or Bi-monthly and the forth one can be either Yes or No and the lat two is just date.
I have to do it one segment of a time, that I ok. Doesn’t need to be fancy.
Hope you can help.
Ekopalypse last edited by
from the given data I would think this should do it
guy038 last edited by guy038
Hello, @dennis-andersen, and All,
Ah ! @ekopalypse beat me to it ;-))
This other regex S/R should also work :
As usual, the in-line modifiers
(?-si)ensure that :
Any dot regex symbol
.represents a single standard character ( NOT like-breaks )
The search is processed in a non-insensitive way
Then the regex contains two alternatives, separated by the
|alternation symbol. So the regex engine searches, from current location, for, either :
Some horizontal blank characters, after a
4-digitsnumber, at beginning of lines and, due to the
\Ksyntax, only matches these blank char(s)
Some horizontal blank characters, ONLY IF NOT followed by a non-null range of standard chars before the periodicity field
\Ksyntax, you cannot use the step by step replacement with the
Replacebutton. You must use the
Replace Allbutton, exclusively
BTW, @dennis-andersen, you very well described your goal and data which greatly helped us to figure out regex solutions ;-))
You guys are the best!!!
One day I hope to be half as smart as you!!
I got help and I learned something. Best day of the year so far…
Thanks and have a great day.
Makwana Prahlad last edited by
List itemIdentify the common text string you wish to remove, in this example, “Accepts Google Pay”
List itemClick the Column Options dropdown arrow and select Set regular expression. The Set regular expression dialog box appears.
List itemIn the Match box, enter “Accepts Google Pay\s+(.+)” to identify the Accepts Google Pay string and then capture the remainder of the string in the first capture capture group.
List itemIn the Replace box, enter $1 to print out the first capture group. The prefix disappears from the column data, leaving the remainder of the string intact.
I hope this information will be usefull for you.