How can this be done?

[wildchild_pt]

Hi,

I am exporting from a database to an excel file, and will need to clean up some fields in the excel file before I can upload that data to a different database.
in one particular cell on the exported file, where I have listed the names and emails of the company contacts, there is a value which indicates if the email address of the contact should be listed or not.
At this moment I cannot manipulate the original database to export the data correctly so all emails appear, and was wondering if there is any possible way that a "rule" could be made to make two changes to this field, firstly, to put a line break after the "N", and also to delete the email address that precedes every letter "N", leaving all emails that precede the letter Y (N=do not display email, Y=Display email).

This data is to be imported to another database which will read this field and create from here a list of contacts from each company, but as the data is, all emails will be published and the contacts will become all jumbled not having one line for each contact.

See below an example of the field in question and how it should look after the clean up;

Data in original field;

[Dr. Paulo Silva | Managing Director | paulo@aguirrenewman.pt | NDrª. Patrícia de Melo e Liz | Managing Partner | patricia@aguirrenewman.pt | NDr. Miguel Bacalhau | Dir. Avaliações | miguel@aguirrenewman.pt | YDr. Eduardo Fonseca | Dir. Escritórios e Retalho | eduardo@aguirrenewman.pt | NDrª Joana Rodrigues | Dir. Arquitect. | joana@aguirrenewman.pt | YDr. Luís Reis | Dir. Indústria | luis@aguirrenewman.pt | NDr. João Inácio | Dir. Gestão de Imóveis | joao@aguirrenewman.pt | N]

after clean up;
[Dr. Paulo Silva | Managing Director | | N
Drª. Patrícia de Melo e Liz | Managing Partner | | N
Dr. Miguel Bacalhau | Dir. Avaliações | miguel@aguirrenewman.pt | Y
Dr. Eduardo Fonseca | Dir. Escritórios e Retalho | | N
Drª Joana Rodrigues | Dir. Arquitect. | joana@aguirrenewman.pt | Y
Dr. Luís Reis | Dir. Indústria | | N
Dr. João Inácio | Dir. Gestão de Imóveis | | N]

Thanks in advance for any help.
Carlos

 

[ranman256]

I pasted the data in cell A1, then ran this macro to break up the records:

Sub ParseRecs()
Dim vLine, vPart1, vPart2, vWord, vKeep, vBlock1, vBlock2, vBlock
Dim i As Long, j As Long, k As Long, m As Long, p As Long


Range("A1").Select
vLine = ActiveCell.Value
While vLine <> ""
    i = InStr(vLine, "@")
    vPart1 = Left(vLine, i)
    vPart2 = Mid(vLine, i + 1)
    vBlock1 = vPart1
    
    k = InStrRev(vPart1, "|")
    vPart1 = Left(vLine, k)
    
    j = InStr(vPart2, "|")
    vKeep = Mid(vPart2, j + 2, 1)        'get Y/N
    vPart2 = Left(vPart2, j + 2)
    vBlock2 = vPart2
    
    vBlock = vBlock1 & vBlock2
    m = Len(vBlock)
    
    Select Case vKeep
       Case "N"
       vWord = vPart1 & " | N"
       
       Case "Y"
       vWord = vBlock1 & vPart2
    End Select
    ActiveCell.Offset(0, 2).Value = vWord
    ActiveCell.Offset(1, 0).Select   'next row for output
    
    Debug.Print Left(vLine, m)
    vLine = Mid(vLine, m + 1)
Wend
End Sub

 

[wildchild_pt]

First and foremost I would like to thank you ranman256 for your very prompt response and solution. I gave it a try and it does pretty much what i need, however I would require a slight tweek if possible, what the code does at this moment is it copies the entire cell and replicates it with the desired results a couple of columns across, and creating a new row per contact, the problem with this solution is that it will overwrite the existing data that is present in the other cells. The work book that i would like to use this on has many columns and rows, each row refers to a client, and each column is a field of data, so it is necessary to keep all the data in the same row (cell) to keep the contacts associated to the same company.

Is it possible to change this code for this to be done in the same cell and substitute the existing data with the formatted data.
I would also like to use this to fix the data in the entire column, as opposed to only one cell, is it possible to edit this to make it run on an entire column.

On the document that i have this information appears in column O.

 

[wildchild_pt]

Hi, Just checking in to see if anybody can find a solution to the necessities of my query. ranman256 supplied a a great start, but i need some help to tweek this a little to do what I require.

Thanks in advance for any help supplied

 

[ranman256]

here's a tweek to make the macro loop thru all rows in col A. (change if needed)

Sub ParseRecs()
Dim vLine, vPart1, vPart2, vWord, vKeep, vBlock1, vBlock2, vBlock
Dim i As Long, j As Long, k As Long, m As Long, p As Long
Dim shtTarg As Worksheet, shtSrc As Worksheet


Set shtSrc = ActiveSheet
Worksheets.Add 1
Set shtTarg = ActiveSheet


shtSrc.Activate
Range("A1").Select
While ActiveCell.Value <> ""
    
    vLine = ActiveCell.Value
    shtTarg.Select           'goto target sheet
    While vLine <> ""
        i = InStr(vLine, "@")
        vPart1 = Left(vLine, i)
        vPart2 = Mid(vLine, i + 1)
        vBlock1 = vPart1
        
        k = InStrRev(vPart1, "|")
        vPart1 = Left(vLine, k)
        
        j = InStr(vPart2, "|")
        vKeep = Mid(vPart2, j + 2, 1)        'get Y/N
        vPart2 = Left(vPart2, j + 2)
        vBlock2 = vPart2
        
        vBlock = vBlock1 & vBlock2
        m = Len(vBlock)
        
        Select Case vKeep
           Case "N"
           vWord = vPart1 & " | N"
           
           Case "Y"
           vWord = vBlock1 & vPart2
        End Select
        ActiveCell.Offset(0, 2).Value = vWord
        ActiveCell.Offset(1, 0).Select   'next row for output
        
        'Debug.Print Left(vLine, m)
        vLine = Mid(vLine, m + 1)
    Wend
      'back to source sheet
    shtSrc.Activate
    ActiveCell.Offset(1, 0).Select   'next row for input
Wend


Set shtSrc = Nothing
Set shtTarg = Nothing
End Sub

 

[wildchild_pt]

Thanks for the Tweek, but I can seem to get it to work