How to trim starting spaces of entire column in LibreOffice or Google sheets?

Question

I'm a bit of a newbie at this, so hope to get some help.

I have a large spreadsheet where columns C and D each have a blank space before the data in each column. Can some one please explain me how to trim an entire column to remove starting spaces in LibreOffice of Google Spreadsheets

Answer 1

In LibreOffice:
1. Select the cells you want to change
2. Edit -> Find & Replace
3. Find: ^\s+
4. Replace:
5. Other Options - Regular expressions: ON
6. Other Options - Current selection only: ON
7. Click Replace All

Answer 2

In Google Sheets, I would do the following.

First, enter the formula =arrayformula(trim(C:D)) in some cell of the first row, for example E1. It will fill two columns (E and F) with trimmed values (removing spaces at the beginning and end of each string).

Then copy the contents of columns E-F and paste values only in C-D; this is done with Ctrl-Shift-V, or by selecting "paste special -> values only" from the context manu.

Answer 3

I'm sure the other answer works, but if you just need to do it once, I have an easy option. Download Sublime Text or some other text editor. Look for spaces at the beginning of a string (using ("^ ") without quotes should do it), and erase them.

Text editors are really helpful for normalizing data like this when you don't need updates in the future.

Answer 4

In LibreOffice you can trim all spaces (beginning and end) via the "text to column" function:

Data -> "Text to Column"

Then assure the column is not being split (e.g. select tab als separator, if no tabs are present) and select "Trim spaces"