TRIM function not working

Manolocs

Active Member
Joined
Mar 28, 2008
Messages
340
I have no idea why this happens, I already followed threads from this forum and use the following formulas and macros but still can not get ride of the spaces in front of the cells :(
Any help is more than welcome.
:rofl:

=TRIM(A1)
=SUBSTITUTE(A1,CHAR(160),"")
=TRIM(SUBSTITUTE(D1,CHAR(160),CHAR(32)))
Code:
Sub RemoveLeadingSpace()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Possibly the leading characters are not realy spaces.
Did you verify the code for the first character, e.g.: =CODE(A1)
 
Upvote 0
Maybe they're not spaces? Try this to find out:

1. Copy the following macro into your workbook:

Code:
MsgBox Asc(CStr(Left(ActiveCell, 1)))

2. Ensure the cursor is on the cell with the data

3. Run the macro from step 1 and if the result is not 32 the character is not a space. Look down the Dec column of this reference to determine what it is.

HTH

Robert
 
Last edited:
Upvote 0
Clearly they aren't actually spaces then. What does this show:
Code:
Sub FirstChar()
msgbox ascw(left$(range("A1").value, 1))
end sub
 
Upvote 0
Then it is a space and trim should work. What does this return:
Code:
Sub FirstChar()
msgbox ascw(left$(Trim(range("A1").value), 1))
end sub
 
Upvote 0
Also, what does this return (just make sure the cursor is on the relevant cell to start with):

Code:
MsgBox Len(ActiveCell) & vbNewLine & Len(Trim(ActiveCell))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top