I'm using Google Sheets, in which I have a single column (call it Column A) that contains plain text data with a lot of leading and trailing spaces (in addition to non-capitalised words, but that's a problem for another time).
I found the following formula to trim all leading and trailing spaces outside of words from this article here:
=TRIM(CLEAN(SUBSTITUTE(A3,CHAR(160)," ")))
…but by the way I've understood it, the formula is intended to be entered into a new column of data (Column B), which would duplicate the text I currently have in Column A, just without the spaces.
Is there any way at all to do away entirely with the extra step involving Column B, and instead apply the formula directly to Column A to avoid duplicating the data? This, to me, seems like the more efficient way to do things, and it would save me a lot of time and effort.
Put another way, I suppose the question is whether a cell can contain both plain text and a formula at the same time. The solutions for this I've come across so far seem to apply to Excel or simply don't work.
Best Answer
Yes, you can edit data in place in Google Spreadsheets. You do it by using Google Apps Script: from the menu Tools, select Script Editor and take it from there. The scripting language is JavaScript, though, so you can no longer directly use spreadsheet formulas.
However, in the spreadsheet world, scripting to edit data in place is typically considered the last resort. If you have a working formula, why not just use its results in Column B? As the data is not edited in place, it's easier to make corrections, there are no side effects and the results are immediately visible too. Immutable data is one of the principles behind functional programming.