How to remove trailing & leading spaces in a cell?

LearnerExcel · #1 01-31-2018, 02:36 PM

How can I remove the trailing & leading spaces even if it is in brackets from the cell?
The following code is fine but for MS Access. How can I use the similar code in Excel to get the result?

Code:

Private Function Spaces(strText As String) As String
        Return String.Join(" ", strText.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
    End Function

p45cal · #2 02-01-2018, 05:17 PM

This might do it:

Code:

Private Function Spaces(strText As String) As String
Spaces = Replace(Replace(Application.Trim(strText), "( ", "("), " )", ")")
End Function

If not, supply a bunch of strings with before and after expectations.

LearnerExcel · #3 02-02-2018, 02:24 AM

Thanks. I don't know writing VBA code for Excel. I need to create a MACRO to update the selected column. How can I do this?

p45cal · #4 02-02-2018, 03:05 AM

OK. Supply a sample file. This'll also help so we know what sort of strings you want to convert and how you expect them to look afterwards.

LearnerExcel · #5 02-04-2018, 10:32 AM

Actually, I need the following things to be done when I click on any column and run a macro.

Remove trailing & leading spaces
Remove spaces before and after the dash/minus sign "-"
No single space is required inside the brackets "()"
Single space is required in front of bracket

p45cal · #6 02-04-2018, 12:20 PM

…and it looks like you need to alter spaces around the * character too.
The attached has a user-defined function Spaces, whose code is:

Code:

Function Spaces(strText As String) As String
Spaces = Application.Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Application.Trim(strText), " -", "-"), "- ", "-"), " *", "*"), "* ", "*"), "(", " ("), ")", ") "), "( ", "("), " )", ")")) 'needed?
End Function

This function can be used on the sheet directly (see sheet Remove Spaces), or from a macro (see sheet Remove Spaces (2))
Using it on a sheet is straightforward.
To use it from a macro, you first select the cells you want to process, then run the macro blah. This macro is called for you when you click the button on sheet Remove Spaces (2), but remember to select the cells you want processing before you click the button. It permanently replaces the contents of those cells.
On both sheets, there's a checking formula to demonstrate that the resulting values are exactly the same as desired.

LearnerExcel · #7 02-04-2018, 01:40 PM

Wonderful! Thanks a lot for your time and kind support. May I please request you for one more favor that can I add one space after period "." wherever in the sentence it is found.

Like:
Ravish.Mokkala = Ravish. Mokkala

p45cal · #8 02-04-2018, 02:16 PM

Code:

Spaces = Application.Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Application.Trim(strText), ".", ". "), " -", "-"), "- ", "-"), " *", "*"), "* ", "*"), "(", " ("), ")", ") "), "( ", "("), " )", ")"))

LearnerExcel · #9 02-04-2018, 08:22 PM

Once again, thank you so much for the code which saved my time to manually correct all the data.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can I remove spaces between only Chinese characters?	sivartnosredna7	Word	2	01-09-2017 05:33 AM
How to remove spaces from Cells which are at the end of value?	LearnerExcel	Excel	2	12-19-2016 01:10 PM
Mail Merge - Trailing Spaces	osucjb	Mail Merge	5	10-28-2016 12:03 AM
Get rid of leading spaces before paragraphs	fig000	Word	11	03-14-2016 01:59 PM
editing text and remove spaces	romanticbiro	Word VBA	5	07-04-2014 07:42 PM