Format from column to single cell

**cjewell** · 02-17-2014, 08:54 AM

I am a macro beginner and I have a column of numbers that I need to reformat. I created a macro to copy/paste special/transpose to get the data into separate columns. I then used & to get them into one cell. I can only have five sets of # in each cell. The macro works but it replaces the previous set of accounts. Is this the best way to reformat these numbers? If so, how do I correct the macro so that it doesnt replace the data i already reformatted?

I have attached a sample spreadsheet. Hope it worked!

Thanks so much!

**Olly** · 02-17-2014, 09:46 AM

Not sure I am quite clear on the requirement - are you trying to move all the numbers from Column A to Column K, but concatenated into groups of 5?

If so, try:

Sub foo()
Dim i As Integer, lRow As Long
Application.ScreenUpdating = False
lRow = 1
Do Until Range("A1") = ""
    For i = 1 To 5
        Range("K" & lRow).Value = Trim(Range("K" & lRow).Value & " " & Range("A" & i).Value)
    Next i
    Range("A1:A5").Delete Shift:=xlUp
    lRow = lRow + 1
Loop
Application.ScreenUpdating = True
End Sub

**cjewell** · 02-17-2014, 10:45 PM

Yes, I need to reformat these numbers into a single cell and each cell can only have 5 sets of #'s. I tried the above and it didnt work? Not sure if I'm doing something wrong?

Thank you

**Olly** · 02-18-2014, 04:09 AM

Expand on "didn't work"... What exactly happened? Worked perfectly for me, on the source data you posted.

Format from column to single cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Format from column to single cell

Re: Format from column to single cell

Re: Format from column to single cell

Re: Format from column to single cell

Thread Information

Users Browsing this Thread

Similar Threads

Multiple Date format in single column

Concatenate 2 columns into a single column but format cell is not copying

Convert single column to table format

How do I copy data in single cell format to a merged cell format

[SOLVED] Format a single column into a row

Bookmarks

Bookmarks

Posting Permissions