8

In excel, TRIM() will remove all spaces before and after text, while also removing any duplicate spaces in between words.

Is there a formula or combination thereof that will do the same as TRIM() but leave spaces between words as-is?

In the following example, I'm looking for a formula that will accomplish that of the fictitious formula "WXYZ":

TRIM(" Omicron Persei 8 ") = "Omicron Persei 8"
WXYZ(" Omicron Persei 8 ") = "Omicron Persei 8"

Note that I've read somewhere that TRIM() in VBA will work like that of WXYZ above. However, I'm looking for a formula solution.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Bort
  • 493
  • 1
  • 8
  • 26
  • Very Good Question! If you don't want to use VB Script, it's pretty difficult. I can't think of anything. Just brainstormed and came up with trimming just the leading spaces. Would it work? – Chaos Legion Nov 17 '16 at 13:54
  • @ChaosLegion I have some spreadsheets that have values with spaces before and after the actual data. The cells from that sheet are referenced in a separate document. The issue is that once those cell values are put in a new document, they are compared to other cells. So leading and trailing spaces cause comparisons to fail. But the in between space cannot be removed because external software needs them to be there for further comparison. – Bort Nov 17 '16 at 14:23
  • I know that there are ways around using a formula solution. But since I couldn't find a formula, I thought I'd post it here to help out others in the same situation. – Bort Nov 17 '16 at 14:25
  • Are the spaces consistently the same, or are they differing lengths? – Glitch_Doctor Nov 17 '16 at 15:46
  • 1
    I mean you could always create the VBA trim as a user defined function `WXYZ` then use that as a formula – Glitch_Doctor Nov 17 '16 at 15:50
  • Seems to be a duplicate of [this](http://stackoverflow.com/questions/9578397/remove-leading-or-trailing-spaces-in-an-entire-column-of-data), perhaps give it a quick read, theres a post referenceing a Ron De Bruin page about cleaning up data. It seems useful, I would have taken some time to apply it but I have a train to go catch. – Glitch_Doctor Nov 17 '16 at 16:00
  • @Glitch_Doctor - The leading and trailing spaces are of random lengths. The Q you linked to does not address my issue about keeping in between whitespace alone. The answers to that Q relate to using TRIM() (which I specifically can't use) and dealing with non-breaking spaces, which is not my issue. – Bort Nov 17 '16 at 16:19
  • in the comments they stated they needed the doubles in between. – Glitch_Doctor Nov 17 '16 at 16:25
  • didnt read further, sorry – Glitch_Doctor Nov 17 '16 at 16:26
  • 1
    @Glitch_Doctor - Your idea of using a user defined VBA function isn't bad if working within the same document. I wanted a pure formula solution though, so that other users who don't fully understand formulas or VBA can just copy and paste the formula into different documents without being confused. – Bort Nov 17 '16 at 16:29
  • If you are, like I was, looking for a way to remove only the leading spaces (using an Excel formula) then I've created another question https://stackoverflow.com/questions/75377850/an-excel-formula-for-remove-only-leading-spaces-in-a-cell/75377964#75377964 and it borrows from @aakash answer below. – Joel Butler Feb 07 '23 at 19:26

4 Answers4

5

I believe this should work (assuming your string is located at A1):

=MID(A1, FIND(LEFT(TRIM(A1),1),A1), (LEN(A1)-MATCH(RIGHT(TRIM(A1),1),INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0)-FIND(LEFT(TRIM(A1),1),A1)+2)

FIND(LEFT(TRIM(A1),1),A1) returns the location of the first non-space character in the string

MATCH(RIGHT(TRIM(A1),1),INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0) returns the location of the last non-space character in the string from right-to-left.

aakash
  • 143
  • 6
  • I tried your answer right after you posted it and it appears to work. But then SE said your answer was edited. But I don't see anything about your answer being edited anymore...did you change anything? – Bort Nov 17 '16 at 16:34
  • Just added the assumption of the string being located at A1 (no change to formula). – aakash Nov 17 '16 at 16:34
  • It's not pretty, but it does the job. I'll accept this if no one has a better solution soon. – Bort Nov 17 '16 at 16:38
2

How would this look in Excel 365? A bit easier I think with let, sequence and xmatch but not particularly short:

=IFERROR(LET(seq,SEQUENCE(LEN(A2)),
array,MID(A2,seq,1),
start,XMATCH(TRUE,array<>" "),
finish,XMATCH(TRUE,array<>" ",0,-1),
MID(A2,start,finish-start+1)),"")

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Confirmed working! – Bort Feb 07 '23 at 15:34
  • 1
    Just for fun, since this question is tagged somewhere else I came across your post. I came up with `=LET(x,TEXTSPLIT(A2," ",,1),TEXTJOIN(DROP(DROP(TEXTSPLIT(" "&A2&" ",x),,1),,-1),,x))` – JvdV Feb 07 '23 at 20:06
  • 1
    @JvdV Your formula also works! Why not post it as an answer? – Bort Feb 08 '23 at 02:50
1

Just to add to all the valuable content:

enter image description here

Formula in B1:

=LET(x,TEXTSPLIT(A2," ",,1),TEXTJOIN(DROP(DROP(TEXTSPLIT(" "&A2&" ",x),,1),,-1),,x))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    I have changed this to the accepted answer. It's much shorter than the other solutions and I wasn't able to find any inputs that cause a deviation in output from the other ones. – Bort Feb 14 '23 at 17:15
0

A Formula Array could also be used.

Assuming the string is located at A1 enter this Formula Array in B2. It's highly suggested to ensure this part of the formula ROW(B:B) refers always to the same column were the formula is located (column B in this case), this is in order to avoid the formula returning an error if the column to which it refers is deleted.

=MID($A1,
FIND(LEFT(TRIM($A1),1),$A1),
1+MAX(ROW(B:B)*(ROW(B:B)<=LEN($A1))*(MID($A1,ROW(B:B),1)<>" "))
-FIND(LEFT(TRIM($A1),1),$A1))

FormulaArrays are entered pressing [Ctrl] + [Shift] + [Enter] simultaneously, you shall see { and } around the formula if entered correctly

As regards the formula provided by @Aakash I suggest to replace the INDIRECT function in this part:

-ROW(INDIRECT("1:"&LEN($A7)))

with this: 

-ROW(B:B)

So the formula will become Non-Volatile: 

=MID($A1,
FIND(LEFT(TRIM($A1),1),$A1),
(LEN($A1)-MATCH(RIGHT(TRIM($A1),1),INDEX(MID($A1,LEN($A1)-ROW(B:B)+1,1),0),0)
-FIND(LEFT(TRIM($A1),1),$A1)+2))
EEM
  • 6,601
  • 2
  • 18
  • 33