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
---------------
Jacob Skaria


"Chris Maddogz" wrote:

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

Thanks again Jacob works a treat

"Jacob Skaria" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "Chris Maddogz" wrote:
>
> > 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
> >