CODEWITHSUNDEEP
excelexcel-formulatrimremoving-whitespace
Joel Butler
Joel Butler

Reputation: 280

Excel formula for removing only leading spaces in a cell (leaving all other spaces alone including trailing)

I've got a column of data where there might be any number of leading space characters. The Excel TRIM function comes to mind but it removes leading, trailing and also replaces consecutive spaces between letters with a single space. I just want to remove all of the leading spaces and do that using a formula; trailing spaces are to be left alone.

As an example, I'd like

 Cell 1
Cell 2
Cell  3
   Cell 4

to become:

Cell 1
Cell 2
Cell  3
Cell 4

Upvotes: 2

Views: 754

Answers (1)

Joel Butler
Joel Butler

Reputation: 280

Here is something that is somewhat easy-to-parse), is slightly sloppy BUT can get the job done (at least it did for me). Building off of the aakash answer and realizing in my own situation I just needed to remove leading spaces I came up with this (using A1 as the example cell):

=MID(A1,FIND(LEFT(TRIM(A1),1),A1),LEN(A1))

...so where this is sloppy is that the LEN, used by MID, could be longer than the remaining length (where/when some leading spaces have been trimmed)...but it turns out it doesn't matter (I am pretty sure in my case and this is where a text compare tool.

All of this is probably an indication that I have not learned VB and while it might be helpful I feel like I never will, so I hack my way there with this type of thing for formulas.

Upvotes: 3

Related Questions