Remove leading and trailing spaces but not spaces in the middle of a string

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
Good afternoon,

That's it, really. I'm comparing two lists that look identical at first glance, but some have trailing and leading space characters.
I can't use TRIM as this would remove all the space characters from within the string too, which I don't want.

As an enhancement to this, could anyone suggest a way to replace multiple space characters within a string with a single space, regardless of how many occurrences of multiple sequential spaces were found?

e.g. Cat{Space}{Space}{Space}Dog{Space}{Space}Horse would become Cat{Space}Dog{Space}<space></space><dog><space></space>Horse

As always, thanks in advance.

Pete</dog>
 
Last edited:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Actual I think the TRIM function should do what you want. It will leave 1 space between words.
Excel Workbook
ABC
1cat dog horse cat dog horse
2
Sheet
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
I stand corrected, colleagues - I thought that TRIM removed ALL spaces. Thank you both.
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
To finish this thread off, whilst =TRIM() in Excel removes spaces as described above, TRIM() in VBA only removes leading and trailing spaces - NOT those within the string.

To get the result I want in VBA, I have to use:

Code:
Selection.Formula = Application.WorksheetFunction.Trim(Selection.Formula)

Thanks again for your assistance & have a good evening

Pete
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top