Deleting characters in a column of cells -converting to text strin

[Chris Maddogz]

I have a column of data (unknown in depth) in A

The data starts in A2.

Each cell contains 6 characters with the last 3 characters as .AX (eg AGK.AX).

I would like to remove the ".AX" from every cell in the column and move it
to its adjacent position in column B until it finds no data in a cell in
column A.

I would then like to copy that entire resultant column B data into cell C2
but as a text string with imbedded spaces between each piece of data.

e.g.
Cell A2 has AGK.AX becomes AGK in B2
Cell A3 has AMC.AX becomes AMC in B3
Cell A4 has AMP.AX becomes AMP in B4
etc until no data incolumn A

After processing the entire column A into column B Cell C2 becomes AGK AMC
AMP etc

Thanks

 

[Jacob Skaria]

Hi Chris

Try the below which works on active sheet ..

Sub Mac()
Dim lngRow As Long, lngLastRow As Long
Dim strData As String
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
If Range("A" & lngRow) <> "" Then
Range("B" & lngRow) = Split(Range("A" & lngRow), ".")(0)
strData = strData & " " & Split(Range("A" & lngRow), ".")(0)
End If
Next
Range("C2") = Trim(strData)
End Sub

If this post helps click Yes

 

[Chris Maddogz]

Thanks again Jacob works a treat

Hi Chris

Try the below which works on active sheet ..

Sub Mac()
Dim lngRow As Long, lngLastRow As Long
Dim strData As String
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
If Range("A" & lngRow) <> "" Then
Range("B" & lngRow) = Split(Range("A" & lngRow), ".")(0)
strData = strData & " " & Split(Range("A" & lngRow), ".")(0)
End If
Next
Range("C2") = Trim(strData)
End Sub

If this post helps click Yes