Trim Macro

[danjw_98]

I would like to trim data (removing extra spaces) in column b and keep the trimmed data in the same column b - is there possibly a macro that can do this.

thanks...

 

[kkknie]

This should do it...

Sub TrimIt()

Dim i as Long

For i = 1 to Range("B65536").End(xlUp).Row
   Range("B" & i).Value = Trim(Range("B" & i).Value)
Next

End Sub

K

 

[danjw_98]

I tried this but it got a message saying wrong number of arguments or invalid property assignments?

I tried the below macro but gets #NAME? in the cell - it appears the command is similar to what you used. please help

Sub TrimTest()

Dim Rng As Range, Cl As Range

Set Rng = Range("A1", Range("A65536").End(xlUp)).Offset(1, 1)

For Each Cl In Rng

Cl.Value = "=trim(Cl.Value)"

Next Cl

End Sub

thanks...

 

[danjw_98]

Looks like i got it to work using the below macro

Sub TrimMacroRuns()

Dim Rng As Range, Cl As Range

Set Rng = Range("A1", Range("A65536").End(xlUp)).Offset(1, 1)

For Each Cl In Rng

Cl.Value = Application.trim(Cl.Value)

Next Cl

End Sub

 

[Ekim]

You may wish to include the CLEAN worksheet function in the macro to remove unprintable characters from your range. This is particularly useful if you are copying data from the Net or you are downloading your data from a mainframe. The CLEAN function will remove CHAR(0) through CHAR(31), Char(129), Char(141), Char(143), and Char(144), but it has no effect on Char(160) – a hard breaking space..

The macro is set for column B:

Sub trimAnd Clean()
Dim x As Long
    x = Range("B63556").End(xlUp).Row
    For i = 1 To x
        Cells(i, 2) = Application.Clean(Application.Trim(Cells(i, 2)))
    Next
    
On Error Resume Next
Columns(2).Replace What:=Chr(160), Replacement:="", LookAt:=xlPart

End Sub

Regards,

Mike

 

[danjw_98]

Thanks...much appreciated....

 

[md_iffu]

Hi,

The provided code, doesnt works for me, I get a popup error "compilation error"

Am trying to trim R column and paste it in the same "R" column.
Kindly assist.

Regard,
Mohammed Irfan.

 

[hiker95]

md_iffu,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


You have not given us any examples of what is in column R, and, you have not displayed what the results should look like.

Here is a macro solution for you to consider that does not do any looping.

Sample raw data in the active worksheet:

Excel 2007
	R
1	this is a test
2	this is not a test
3	this is a test
4	this is not a test
5	this is a test
6	this is not a test
7
Sheet1

After the macro:

Excel 2007
	R
1	this is a test
2	this is not a test
3	this is a test
4	this is not a test
5	this is a test
6	this is not a test
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub TrimColumnR()
' hiker95, 05/20/2015, ME83611
With ActiveSheet.Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the TrimColumnR macro.

 

[md_iffu]

Hi,

Thank you for the prompt reply, really appreciate it.

Am using windows 7 OS - desktop.
Am using excel 2013 and I have little bit knowledge on excel

The column which am refering is "R" in which I have the locations mentioned.
Ex: Brentford, UK .

As per the above example, when I run the macros, the result must delete the last word end space.
Result must be: Brentford, UK.

 

[hiker95]

md_iffu,

2. Are you using a PC or a Mac?

Thank you for the prompt reply, really appreciate it.

You are welcome.

So that we can get it right on the next try:

Can we see at least 5 example strings, and, their results?