Log In
Register
Home
December 5, 2016
Offline
How to write a macro to remove leading, trailing spaces, line breaks or hidden character etc etc in the range cells selected.
I convert the data from Pivot Table to value but found can not perform the calculation in the converted sheet due to above.[ pls refer attached]
September 9, 2020
Offline
Hi David,
Here is a code that does what you ask. :
Sub Space()
Dim Ligne As Integer, cell As Range
With Sheets("Sheet2")
'Determines the last line
For Each cell In .UsedRange
'Removes unnecessary and other spaces in the cell
'TAB
cell = Replace(cell, Chr(9), "")
'Line Feed
cell = Replace(cell, Chr(10), "")
'space
cell = Replace(cell, Chr(160), "")
cell = Application.WorksheetFunction.Trim(cell)
cell = Application.WorksheetFunction.Clean(cell)
Next
End With
End Sub
BR,
Lionel
December 5, 2016
Offline
Thanks Lionel, but cells Leading spaces still can not remove.
September 9, 2020
Offline
David,
On my pc, all the spaces are deleted. Can you provide a file with the problem?
BR,
Lionel
December 5, 2016
Offline
Thanks Lionel your support.
Pls refer attached again after executed the macro, some cells highlighted, for samples consequetive leading spaces,comma, still can not removed, so can not convert to value attribute for calculation. Or, may it be we are using Chinese Office 2019 version , some character codes net yet configured to remove by this Macro?
December 5, 2016
Offline
BTW , when you define the parameter what do your mean by Ligne?
Dim Ligne As Integer, cell As Range
Trusted Members
December 20, 2019
Offline
Slightly different take on it - might help with non numbers in chinese?
This Macro loops through each cell in a selectoin and checks if each character is a number - not sure of the Chinese ascii character set so it might need tweaking on this row If TestN >= 48 And TestN <= 57 = True Then
You must select a range for it to work
Sub NumbersOnly()
Dim n As Integer
Dim QtyOfN As Integer
Dim c As Range
Dim r As Range
Dim S As String
Dim TestN As Variant
Set r = Selection
For Each c In r
c.Activate
QtyOfN = Len(c)
If QtyOfN = 0 Then GoTo x
For n = 1 To QtyOfN
TestN = Asc(Mid(c, n, 1))
If TestN >= 48 And TestN <= 57 = True Then
S = S & Chr(TestN)
End If
Next n
ActiveCell = S + 0
S = ""
x:
Next c
End Sub
December 5, 2016
Offline
Thanks Purfleet, Lionel , this works out exactly and precisely remove all leading and trailing spaces in every cells.
My sincere thanks again, really treasure the macro skills and lessons derived from this forum
December 29, 2020
Offline
Hi,
Just out of passing interest, I looked at the characters in the workbook with a function which I have that lists all characters in strings.
It looks as though there are a lot of
Chr(160)s (Non-breaking space )
and
a few Chr(32)s ( space )
_.______________________________________________
Here is another macro. It should remove all the spaces, Chr(160)s and Chr(32)s, in a selected range
Sub Trash160and32() ' https://www.myonlinetraininghu.....s-in-cells
Let Selection.Value = Evaluate("=If({1},SUBSTITUTE(SUBSTITUTE(" & Selection.Address & ", """ & Chr(32) & """, """"), """ & Chr(160) & """, """"))")
End Sub
_._______________________
Ref:
https://excelribbon.tips.net/T.....paces.html
https://excel.tips.net/T003037.....paces.html
_.____________________________________
Share ‘REMOVE-SPACE.xlsm’ : https://app.box.com/s/k15ug8bm.....edtcatefyl
Share ‘REMOVE-SPACE.xlsx’ : https://app.box.com/s/b9u8m01l.....ju2l4hiaqf
Alan
December 5, 2016
Offline
Thanks Alan the altenative, they also worlk perfectly.
December 29, 2020
Offline
You are welcome. Thanks for confirming that the alternative works for you. That is good to know.
Alan
_.__________________________________________________
P.S.
If you have a very latest version of Microsoft Office, then this simplified code line may work also:
Selection.Value = Evaluate("=SUBSTITUTE(SUBSTITUTE(" & Selection.Address & ", """ & Chr(32) & """, """"), """ & Chr(160) & """, """")")
The above version is likely to work on Microsoft Office from approximately Microsoft Office 2016 and upwards
_.__
But I would recommend using my first alternative, since that should work in all Microsoft Office versions: The following code line should work on both old and new Microsoft Office versions.
Selection.Value = Evaluate("=If({1},SUBSTITUTE(SUBSTITUTE(" & Selection.Address & ", """ & Chr(32) & """, """"), """ & Chr(160) & """, """"))")
( The extra bit.._
If({1},_________)
_.. is one of a few known old “tricks” that makes Evaluate("________") return an array of values.
We are beginning to notice that in newer Microsoft Office versions, these old “tricks” are no longer needed )
Ref:
December 5, 2016
Offline
Thanks Alan, the supplement information.
October 5, 2010
Offline
Hi David,
Use Power Query. The data looks like it's already been imported or copy/pasted - all numbers are text? If you use PQ to bring this data into Excel in the first place, such problems can be dealt with easily.
Creating a table from your data, then opening in the PQ editor, just by converting the numeric columns to number type removes any non-printable chars and white space.
If you still need to do further clean up on text columns, use Transform -> Clean
Text.Clean - PowerQuery M | Microsoft Docs
Regards
Phil
December 5, 2016
Offline
Thanks Philip,another brilliant alternative. Normally the data derive from System Report, so the PQ can come in to help transform data, make our Report much easier to handle.
Just eager to know can we use the PQ to alter the attribute of any column data convert to "Date", "Text", or join two columns or more data base on some specific criteria.
October 5, 2010
Offline
Hi David,
If you mean can you convert columns to different data types in PQ, then yes, very easily.
Likewise, joining columns together is simple in PQ - this is exactly the kind of thing it was built for.
Regards
Phil
1 Guest(s)
