2

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
Bort
  • 493
  • 1
  • 8
  • 26
Joel Butler
  • 240
  • 3
  • 15

1 Answers1

3

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Joel Butler
  • 240
  • 3
  • 15
  • This answer I've supplied was provided by user:6758704 in this question: https://stackoverflow.com/questions/40656326/how-can-i-remove-only-leading-and-trailing-spaces-while-leaving-spaces-in-betwee/75366418?noredirect=1#comment133000851_75366418 – Joel Butler Feb 07 '23 at 19:18
  • 1
    You could also consider Replace as an alternative =REPLACE(A1,1,FIND(LEFT(TRIM(A1),1),A1)-1,"") – Tom Sharpe Feb 07 '23 at 19:58
  • 1
    The 2nd parameter of the `LEFT()` function is optional. You may just leave it out since the default is '1'. – JvdV Feb 07 '23 at 20:11