Stripping Characters from Imported Data?

[Smitty]

Does anyone have a method for automatically stripping characters from imported values?

Context: I have data that is auto-imported into a table with a Date & time column, but unfortunately the data source I'm using (and have little control over) appends "UTC" to the date & time values (e.g., value is "2021-07-09 21:38:32 UTC" instead of just "2021-07-09 21:38:32"). I want to strip out the "UTC" string from those values in my Honeycode table so that data is clean for my uses there (e.g., using that column in filtered views).

[John-4d30]

Yes, you can use replace string in column field and apply it in the formula

[Eric]

If the length of the datetime string is constant up to the 'UTC' portion, you could do LEFT({datetime}, {number of characters up to the last seconds digit}).

eg. LEFT("2021-07-09 21:38:32 UTC", 19) should return "2021-07-09 21:38:32", I believe.

[Pankaj]

Hi @Smitty,

Thanks for getting in touch.

@John-4d30 and @Eric, thank you for your suggestions. @Smitty REPLACE() and LEFT() can be used effectively in your scenario. Alternatively, You might also try using the SUBSTITUTE() function, as below, to handle your situation:

=SUBSTITUTE("2021-07-09 21:38:32 UTC", "UTC","")

I hope this is helpful.

[Smitty]

Thank you, @Pankaj , @John-4d30 and @Eric for the excellent suggestions! I've applied the SUBSTITUTE function and it works very well.