#1
|
|||
|
|||
How to remove trailing & leading spaces in a cell?
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 |
#2
|
||||
|
||||
This might do it:
Code:
Private Function Spaces(strText As String) As String Spaces = Replace(Replace(Application.Trim(strText), "( ", "("), " )", ")") End Function |
#3
|
|||
|
|||
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?
|
#4
|
||||
|
||||
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.
|
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
…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 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. |
#7
|
|||
|
|||
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 |
#8
|
||||
|
||||
Code:
Spaces = Application.Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Application.Trim(strText), ".", ". "), " -", "-"), "- ", "-"), " *", "*"), "* ", "*"), "(", " ("), ")", ") "), "( ", "("), " )", ")")) |
#9
|
|||
|
|||
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 |