Need help with loop, concatenate and trim in Excel, VBA

[cp713a]

Hi,

I need some help. I have a long list of items in column A with extra spaces, such as: Samsung ', I'd like to loop thru the entire column A, trim out all the extra spaces, I think application.worksheetFunction.Trim(A1), then concatenate the a word "Equipment", the output result as: "Samsung Equipment" in the same column A.

I was able to do a single item, but not able to get the loop, which I'm not familiar with.

Much appreciate it,

[anish.ms]

Attach a sample file. However, the following sample code helps to trim the extra spaces including space with special character code 160
Select the cells with data in column A




Sub RemoveSpaces()
Dim myRange As Range
Dim myCell As Range
Set myRange = Selection
For Each myCell In myRange
If Not IsEmpty(myCell) Then
myCell = Application.WorksheetFunction.Trim(WorksheetFunction.Substitute(myCell, Chr(160), "")) & " Equipment"
End If
Next myCell
End Sub

[cp713a]

Please see attached file for sample. So, what I'd like to see is in ColA, I have existing data, and I'd like to loop thru and achieved like the Result in ColB, but without dump the data in another column. So, the existing text in ColA, loops thru that entire column, concatenate word 'Equipment', adding single quotes ('Samsung Equipment') like so around the text. Hope this helps and makes sense.

[p45cal]

Select the cells you want to process first, then try running:
Sub blah()
For Each cll In Selection.Cells
x = Split(cll.Value, "'")
x(0) = x(0) & " Equipment"
cll.Value = Application.Trim(Join(x, "'"))
Next cll
End Sub

[snb]

Sub M_snb()
sheet1.columns(1).replace " "," "
sheet1.columns(1).replace "'","Equipment'"
End Sub

[cp713a]

Select the cells you want to process first, then try running:
Sub blah()
For Each cll In Selection.Cells
x = Split(cll.Value, "'")
x(0) = x(0) & " Equipment"
cll.Value = Application.Trim(Join(x, "'"))
Next cll
End Sub



p45cal, this works thank you very much.:yes