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

Thanks Lionel, but cells Leading spaces still can not remove.

David,

On my pc, all the spaces are deleted. Can you provide a file with the problem?

BR,

Lionel

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?

BTW , when you define the parameter what do your mean by Ligne?

Dim Ligne As Integer, cell As Range

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

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

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.myonlinetraininghub.com/excel-forum/vba-macros/macro-remove-leading-and-trailing-spaces-in-cells

 Let Selection.Value = Evaluate("=If({1},SUBSTITUTE(SUBSTITUTE(" & Selection.Address & ", """ & Chr(32) & """, """"), """ & Chr(160) & """, """"))")

End Sub

_._______________________

Ref:

https://excelribbon.tips.net/T010741_Removing_Spaces.html

https://excel.tips.net/T003037_Removing_Spaces.html

_.____________________________________

Share ‘REMOVE-SPACE.xlsm’ : https://app.box.com/s/k15ug8bmfce2oqewjkfjptedtcatefyl

Share ‘REMOVE-SPACE.xlsx’ :  https://app.box.com/s/b9u8m01le9nly3wgkjyhaiju2l4hiaqf

Alan

Thanks Alan the altenative, they also worlk perfectly.

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: 

http://www.eileenslounge.com/viewtopic.php?f=30&t=35600&p=276868#p276868

http://web.archive.org/web/20200203182942/http://excelmatters.com/2020/01/29/excel-365-evaluate-and-the-type-mismatch-error/       )

Thanks Alan, the supplement information.

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

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.

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