Help writing a Macro

[ollieotis]

Hello,

I have a data table with four fields and approximately 2,500 records that I've been asked to format in a particular manner and I'm struggling with how to do so without writing a macro. The problem is, I'm not very good with VBA and am struggling with how to even start it out. Any help anyone can provide would be greatly appreciated.

Four Fields in the table:

File Number: Unique Alphanumeric Record
Owner: One Name
To: email addresses seperated by semicolon then a single space
CC: email addresses seperated by semicolon then a single space

The format I've been requested to provide, is for all email addresses in the To and CC fields be aligned to the left in one cell as if the text was wrapped, only with clean breaks after the semicolon. Here's an example:

Data layed out in one cell:

mike@abc.com; sean@abc.com; tom@abc.com

want to see it one cell as:

mike@abc.com;
sean@abc.com;
tom@abc.com

Some rows have multiple addresses, others only one and none in the CC. The only consistent component is the fact that they are seperated by a semicolon and single space. The only way I know how to complete this task is to go through them one by one and use the alt enter command.

Any help anyone can provide for an alternate solution would be much appreciated!

 

[Jonmo1]

Might be able to do it with built in functions, no vba or formula...

Highlight all the cells with email addresses laid out like
mike@abc.com; sean@abc.com; tom@abc.com

Press CTRL + H (find/replace)
Find What: " " <----without the quotes (just press space bar)
Replace With: Hold ALT Key, type 010, release ALT key
Replace All

You may have to adjust column width and row height after doing that..

 

[lenze]

Maybe a formula

=SUBSTITUTE(A1,"; ",";"&CHAR(10),1)

Format cells to wrap text
lenze

 

[Domski]

For VBA try:

Sub test()

Dim myRange As Range, strChar As String, x As Long, strSplit As String

For Each myRange In Selection

For x = 1 To Len(myRange.Value)

    If Mid(myRange.Value, x, 1) = ";" Then
    
        strSplit = strSplit & Mid(myRange.Value, x, 1) & vbLf
        
    Else
    
        strSplit = strSplit & Mid(myRange.Value, x, 1)
        
    End If

Next x

myRange = strSplit

Next myRange

End Sub

Dom

 

[ollieotis]

Thank you!

The find replace solution works great in all instances save those where there are a large number of email addresses included. In this case I get an error saying the formula is too long, despite the fact that it's not a formula, only text. I imagine there are too many characters, but am not certain.

Again, thank you!