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

  • Thread starter Thread starter Chris Maddogz
  • Start date Start date
C

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
 
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
 
Thanks again Jacob works a treat

Jacob Skaria said:
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
Click to expand...
 
Back
Top