TextToColumns

[PWD]

Good afternoon I am thinking of using TextToColumns within a macro as I need to select an e-mail address from a cell, provided it doesn't have in it:-

"@nn.com"

The data being supplied to me comes from an external source where there may be any number of e-mail addresses (or none) any (or all) of which may contain "@nn.com" - separated by either a space or a semi-colon.

I have been working up some code that can successfully identify if the sole e-mail contains "@nn.com" or not and if the first e-mail address contains "@nn.com" but I got a bit stuck when it came to figuring out how to identify the total number of e-mail addresses and the the location of a non-"@nn.com" e-mail address if it wasn't the first one. Hence deciding to try to split the cell as I thought it would be easier to identify what I want from individual cells.

However my stumbling block is that there are variable numbers of e-mail addresses within each cell.

All right, I seem to have figured out how to count them:

            AtCount = -1
            NextAt = 1
            FirstAt = 1
               Do While FirstAt <> 0
                       FirstAt = InStr(NextAt, Range("AG" & x), "@", vbTextCompare)
               NextAt = FirstAt + 1
               
               AtCount = AtCount + 1
            
               Loop

Now, how to use that information to enable me to put together something like this?

    Selection.TextToColumns Destination:=Range("AK30"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
        , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), _
        TrailingMinusNumbers:=True

Many thanks,
D€$

 

[SkipVought]

hi,

Text to Columns to find/exclude some text?

How about AutoFilter > Does not contain or contains whatever? Then process visible cells or delete visible cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Thanx Skip, but even if a cell does contain "@nn.com" (an invalid e-mail address) it may still have a valid e-mail address in there somewhere. Basically we do market research and the "nn" represents the client's e-mail address so we don't want to send their employees questionnaires.

Many thanks,
D€$

 

[Andrzejek]

It would be nice to see a sample of your data representing the problems you have, and what you would like to have as the outcome. Pretty much not HOW to do it, but WHAT to do.

Have fun.

---- Andy

 

[SkipVought]

I'd use the Split() function in VBA to parse the data. I assume that you have a list of VALID eMail addresses and all others "@nn.com" would be ignored. Just process in 3 loops...

for each row in sheet.rows
   a = split(row.value, ";")      'you can also check of " " if UBound(a) = 0
   for i = 0 to ubound(a)
      for each r in [YoureMailList]
         if a(i) = r.value then
           'houston, we have convergence!
         end if
      next
   next
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Andy here are some (simplified) examples:

servicedesk@nn.com e.f@nn.com Service@uk.fuj.com sp.monitor@nn.com
h_yun.lu@ns.com prince.itsm@nn.com
Pn-sd-hnz@nn.com HectorAlerts@ca.hj.com

Should produce

Service@uk.fuj.com
h_yun.lu@ns.com
HectorAlerts@ca.hj.com

As I said, there may, or may not, be non-"@nn.com" e-mail address anywhere - there may be up to 12 e-mail addresses.

Many thanks,
D€$

 

[SkipVought]

Should produce" based on what criteria? You have produced NOTHING of substance on which to make a logical decision!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I don't know what's valid as there will be several thousand rows of data.

Many thanks,
D€$

 

[SkipVought]

Sorry, I missunderstood you.

My results
[tt]
Service@uk.fuj.com
h_yun.lu@ns.com
HectorAlerts@ca.hj.com
[/tt]

My code

Sub main()
    Dim r As Range, a, i As Integer, lRow As Long
    
    lRow = 2
    
    For Each r In Sheet2.[Table3[#Data]]
        With r
            a = Split(.Value, " ")
            
            For i = 0 To UBound(a)
                If Not a(i) Like "*@nn.com" Then
                    Sheet3.Cells(lRow, "A").Value = a(i)
                    lRow = lRow + 1
                End If
            Next
        End With
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I forgot that you mentioned that the separator could be either SPACE or SEMICOLON, so...

Sub main()
    Dim r As Range, a, ai As Integer, b, bi As Integer, lRow As Long
    
    lRow = 2
    
    For Each r In Sheet2.[Table3[#Data]]
        With r
            a = Split(.Value, " ")
            
            For ai = 0 To UBound(a)
                b = Split(a(ai), ";")
                
                For bi = 0 To UBound(b)
                    If Not b(bi) Like "*@nn.com" Then
                        Sheet3.Cells(lRow, "A").Value = b(bi)
                        lRow = lRow + 1
                    End If
                Next
                
            Next
        End With
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Thanks Skip. I know you're a big fan of structured tables so I guess that's what you're alluding to in the code? This would be a spreadsheet we'd receive once a week as part of a whole Excel & Access process - containing up to 9,000 rows - so that's why I was coding using the e-mail column "AG". Would there still be an advantage in setting the data area as a table?

Of course the other thing is that there are cells that only have one e-mail address. I hadn't mentioned this as I'd figured how to handle whether or not to include them - apologies - so I guess I just need to test for no space or semi-colon?

Many thanks,
D€$

 

[SkipVought]

I know you're a big fan of structured tables so I guess that's what you're alluding to in the code? This would be a spreadsheet we'd receive once a week as part of a whole Excel & Access process - containing up to 9,000 rows - so that's why I was coding using the e-mail column "AG". Would there still be an advantage in setting the data area as a table?

I would have a workbook that would be my Master. I would IMPORT the data from the weekly into the same sheet each week, via MS Query.

Of course the other thing is that there are cells that only have one e-mail address. I hadn't mentioned this as I'd figured how to handle whether or not to include them - apologies - so I guess I just need to test for no space or semi-colon?

This is not a problem at all. No special coding required to accomodate one occurrence.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}