Is there a way to remove certain data from a String when it is found?
I only want to report the hour the store is Open. So this:
M-F,8am-5pm;SA,Closed;SU,Closed
M,9am-5pm;T,Closed;W,9am-5pm;TH,Closed;F,9am-5pm;SA,Closed;SU,Closed |
would need to become:
M-F,8AM-5PM
M,9am-5pm;W,9am-5pm;F,9am-5pm |
Is there some sort of find and replace I could do?
Thanks in advance for any help.
Solved! Go to Solution.
Hey Brad,
You can try using the Find Replace tool. That might help you.
Hi,
There could be a simpler way (probably) but I had an idea and just kept going down that path.
TEXT TO COLUMNS: I first split the text to columns using the ; as a delimiter. (Nice to know there is a limit of seven columns)
MULTI FIELD FORMULA: set the field to "" if it's closed
FORMULA: Build back the text when the field is not blank and in the second step remove the last ";"
SELECT: Drop the extra fields
Cheers,
Bob
Hi @Brad1!
Not a find and replace solution, but rather a filtering one.
Assigned each row of hours a unique ID with the Record ID tool
Then parsed individual hours into rows, separated on the semicolon, with the Text to Columns tool
Used the Filter tool to filter out rows that contain "Closed"
Rebuilt the hours field by summarizing on the Record ID, and concatenating the hours back together
Yes, this: Replace([Hours_of_Operation], ";SU,Closed", "")
along with 6 others for the rest of the week works as well. This is what I went with to keep the number of tools down in the workflow.