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...

 

[hiker95]

md_iffu,

Here is another macro solution for you to consider based on your single example, and, result.

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).

Sub TrimColumnR_V2()
' hiker95, 05/20/2015, ME83611
With ActiveSheet.Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","" ."","".""),"""")")
End With

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_V2 macro.

 

[md_iffu]

Hi,

Am using a PC.
Below is the example.
in the below column as you can see "location" details.
I want this column to be trimmed and get pasted as values in the same column.

Location

Apex, NC

West Conshohocken, PA

Franklin, MA

Seattle, WA

Toronto, On, Canada

<tbody>
</tbody><colgroup><col></colgroup>

 

[Rick Rothstein]

Here is another macro solution for you to consider based on your single example, and, result.

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).

Sub TrimColumnR_V2()
' hiker95, 05/20/2015, ME83611
With ActiveSheet.Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","" ."","".""),"""")")
End With

Although the example is not clear to me, Message #12 seems to indicate the OP wants something different than what your macro does; however, I just wanted to point out that there is a simpler (and probably quicker) code solution to remove the space from in front of a period...

Sub TrimColumnR_V2()
  Columns("R").Replace " .", ".", xlPart
End Sub

 

[Rick Rothstein]

Am using a PC.
Below is the example.
in the below column as you can see "location" details.
I want this column to be trimmed and get pasted as values in the same column.

What you existing data looks like is not clear because the comment processor on this forum removes multiple contiguous spaces and replaces them with single spaces. Post your existing example data again, but surround it with code tags (use the # button in the Reply windows Ribbon) as that will preserve multiple spaces. Then post your desired result for that data, again, using code tags.

 

[hiker95]

md_iffu,

I want this column to be trimmed and get pasted as values in the same column.

Here is another macro solution for you to consider based on your above quote.

Sample raw data:

Excel 2007
	R
1	Location
2	Apex, NC .
3	West Conshohocken, PA .
4	Franklin, MA .
5	Seattle, WA .
6	Toronto, On, Canada .
7
Sheet1

Cell Formulas
Range	Formula
R2	="Apex, NC ."
R3	="West Conshohocken, PA ."
R4	="Franklin, MA ."
R5	="Seattle, WA ."
R6	="Toronto, On, Canada ."

After the new macro:

Excel 2007
	R
1	Location
2	Apex, NC.
3	West Conshohocken, PA.
4	Franklin, MA.
5	Seattle, WA.
6	Toronto, On, Canada.
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).

Sub TrimColumnR_V3()
' hiker95, 05/22/2015, ME83611
With ActiveSheet.Range("R2:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  .Value = .Value
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","" ."","".""),"""")")
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_V3 macro.

 

[hiker95]

md_iffu,

If my reply #15 is still not correct, then we will need to see your actual raw data workbook/worksheet.

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[Rick Rothstein]

Sub TrimColumnR_V3()
' hiker95, 05/22/2015, ME83611
With ActiveSheet.Range("R2:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  .Value = .Value
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","" ."","".""),"""")")
End With
End Sub

You can delete the code line that I highlighted in red... the Evaluate function in the next line will automatically return values directly, there is no need to change the formulas to values beforehand for it.

 

[hiker95]

You can delete the code line that I highlighted in red... the Evaluate function in the next line will automatically return values directly, there is no need to change the formulas to values beforehand for it.

Thank you.

 

[hiker95]

Private Message from md_iffu:

Thank you

Hi,

Appreciate your quick response on the macro provided #15 is working fine.

Going further if I need any help, will post or check the forum.

Regards,
Mohammed Irfan
md_iffu

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.