Stripping Characters from Imported Data?

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).

1 Like

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

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.

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.

1 Like

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


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.