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
Location |
Apex, NC |
West Conshohocken, PA |
Franklin, MA |
Seattle, WA |
Toronto, On, Canada |
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...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).
Code: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
Sub TrimColumnR_V2()
Columns("R").Replace " .", ".", xlPart
End Sub
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.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.
I want this column to be trimmed and get pasted as values in the same column.
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 ." |
Excel 2007 | |||
---|---|---|---|
R | |||
1 | Location | ||
2 | Apex, NC. | ||
3 | West Conshohocken, PA. | ||
4 | Franklin, MA. | ||
5 | Seattle, WA. | ||
6 | Toronto, On, Canada. | ||
7 | |||
Sheet1 |
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.Rich (BB code):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.
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