Script to replace speacial characters with line breaks

hi friends,

I am new to VBA excel scripting.. I need a script that fulfills the following conditions..

I have an excel sheet which contains the data as the following


"""To:sara;
cc: abi; pup; """
"""To: loe, frez;Str, Peo; lrson
Cc: IT; GS; """
"

each cell in the first column contains the same data.. now I need the output as follows

sara
abi
pup
loe,frez
str,Peo
irson
IT
GC

which means the characters ; : " should be replaced with line break.. and the names should be displayed in the second sheet or some other column one by one.. but in the same column..


could u please help me to do this.. many thanks in advance

Hi Jindon,

tha above code is a good one.. many thanks also some of the data is missing when using the script.. please try the following data and help me to do the same..

"1st Contact: WI-sara
""To: dl it ok leo Basis; anu.grpo@ymail.com;
cc: ITpo, ISD;Jay nimen ;Wuiopt Grzz i, Anu Stena, liza martin""
Doprkm@ymail.com"

your reply is very important to me as this is the one i was expecting

Hi saranyabi,
as an option

Sub ertert()
Dim sp
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    .Replace "*:", ""
    .Replace "; ""*", ""
    .Replace """", ""
    .Value = Application.Trim(.Value)
    sp = Split(Replace(Join(Application.Transpose(.Value), ";"), ";;", ";"), ";")
    .Resize(UBound(sp) + 1).Value = Application.Transpose(sp)
End With
End Sub

hi nilem,

many thanks for your response.. also it shows the following error.. there is no data "there is no data found".. kindly assist with me step by step.. as im new to VBA.. thank you...

If this is not what you are expecting, need to see your desired result in a workbook.

Sub test()
    Dim r As Range, temp, x
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            temp = Replace(r.Value, ";" & vbLf, ";")
            .Pattern = "^[^:]+: *|;[^;,]+$"
            temp = .Replace(temp, "")
            .Pattern = "; *"
            x = Split(.Replace(temp, vbLf), vbLf)
            Sheets("sheet2").Range("a" & Rows.Count).End(xlUp)(2) _
            .Resize(UBound(x) + 1).Value = Application.Transpose(x)
        Next
    End With
End Sub

Hi Saranyabi,
please attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic

hi nilem,

i have attached an excel sheet.. inwhich sheet1 contains input and the sheet2 contains the output data.. kindly help me to do this.. many thanks in advance..

Hi nilem,

could u please help me on this.. i have uploaded the excel sheet...

This should do

Sub test()
    Dim r As Range, temp, x
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            temp = Replace(Replace(r.Value, ";" & vbLf, ";"), vbLf, ";" & vbLf)
            .Pattern = "(^|\b)[^;,:]+: *(?!\n)"
            temp = .Replace(temp, "")
            .Pattern = ";+\n"
            temp = .Replace(temp, vbLf)
            .Pattern = "; *"
            x = Split(.Replace(temp, vbLf), vbLf)
            Sheets(2).Range("a" & Rows.Count).End(xlUp)(2) _
            .Resize(UBound(x) + 1).Value = Application.Transpose(x)
        Next
    End With
End Sub

Dear jindon,

this is a perfect one.. and i tried with one exceptional case.. please refer the following

input:

"""To:rio, martina; tinkil, nio(External);support@ymail.kl
Cc: ITBS; GOAL; """


when generating output the quotes have not been deleted in last line "" displays.. Please check... thanks a lot..

Change one line

            temp = Replace(Replace(r.Value, ";" & vbLf, ";"), vbLf, ";" & vbLf)

to

            temp = Replace(Replace(Replace(r.Value, ";" & vbLf, ";"), vbLf, ";" & vbLf), """", "")

perfect one jindon.. thats juz awesome.... thanks a lot..

You are welcome and thanks for the rep.

Can you mark this thread as "SOLVED"?

ya sure...