Macro to create new line in cell

[BMil8]

Hello,

I have a list of about 1,000 zip codes in a cell, all of which are seperated by a semicolon. After every 7th semicolon I would like to create a new line. (I'm having a problem with text wrapping when loading the data into our software and it needs to be formatted this way.) I could manually do this by using Alt/Enter, but this would take way to long.

Is there a vba code that will look at the cell and enter a new line after every 7th semicolon?

Thanks so much,
Brian

 

[BMil8]

So I guess we could also use after every 42nd character if that makes it easier.

 

[ChrisM]

try this:

Public Sub test()

Dim aStr As Variant
Dim i As Long
Dim sOutput As String
Dim lCount As Long

aStr = Split(Sheet1.Range("H7").Value, ";")

lCount = 7
For i = 0 To UBound(aStr, 1)
    sOutput = sOutput & aStr(i) & ";"
    If i + 1 = lCount Then
        sOutput = sOutput & vbCrLf
        lCount = lCount + 7
    End If
Next i

MsgBox sOutput

End Sub

 

[BMil8]

Hi Chris,

It's odd. Nothing happens except that I get a blank pop-up box.

 

[ChrisM]

Well, you have to adapt the code! It's looking at sheet 1, cell h7. You need to change that to the correct sheet and cell.

It's also not saving the data in the worksheet, instead of the
Message box you need to specify a cell to save to.

You didn't provide all the specific details so I assumed
You just needed the basics.

 

[BMil8]

I'm sorry I'm still kind of new to this. I actually did update the sheet and the cell though. But how do I change it so it removes the message box and puts it in another cell?

Thanks for your help!

 

[ChrisM]

Remove the msgbox line and replace with:

Sheets("yoursheetname").range("a1") = soutput

Change a1 to your desired cell

 

[BMil8]

Chris you rule. You saved me a lot of time. Thanks!