Altering data within a String

Question

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.

Bob_Blackey · Accepted Answer

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

DanS · Accepted Answer

Hey Brad,

I gave you a lazy answer previously.

Attached is a quick workflow that accomplishes what you want for the given data.

Let me know if this works.

estherb47 · Accepted Answer

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

Brad1 · Accepted Answer

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.