Strip out unwanted data from cell

[DavidAC]

good morning,
I have a spreadsheet with circa 10K of rows. one of the cells contains data which needs to be stripped out to only leave the required readable data. The field currently contains a lot unnecessary data

"We're coming to fit your property tomorrow morning. Your appointment slot is between {{"date":{"inputValue":"12/4/2018 8:00:00 AM","requestedFormat":"HH:mm","inputValueFormat":"MM/DD/YYYY hh:mm:ss a"}}} and {{"date":{"inputValue":"12/4/2018 12:00:00 PM","requestedFormat":"HH:mm","inputValueFormat":"MM/DD/YYYY hh:mm:ss a"}}} but we should arrive between {{"date":{"inputValue":"12/4/2018 10:56:00 AM","requestedFormat":"HH:mm","inputValueFormat":"MM/DD/YYYY hh:mm:ss a"}}} and {{"date":{"inputValue":"12/4/2018 11:27:00 AM","requestedFormat":"HH:mm","inputValueFormat":"MM/DD/YYYY hh:mm:ss a"}}}.

it should read as follows

"We're coming to fit your property tomorrow morning. Your appointment slot is between 12/4/2018 8:00:00 AM", and ":"12/4/2018 12:00:00 PM but we should arrive between 12/4/2018 10:56:00 AM"," and 12/4/2018 11:27:00 AM."

any help is appreciated?

 

[steve the fish]

Try running this, changing sheet name to suit:

Dim sh As Worksheet, arr, i As Long

Set sh = Sheets("Sheet2")
arr = Array("{{""date"":{""inputValue"":""", """,""requestedFormat"":""HH:mm"",""inputValueFormat"":""MM/DD/YYYY hh:mm:ss a""}}}")

For i = LBound(arr) To UBound(arr)
    sh.Cells.Replace What:=arr(i), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next