-2

I have a list A1:A4000 want to sort or find cells having special character '@' two times in one cell.

4
  • 2
    And what have you already tried / achieved, dude? Commented Jul 24, 2015 at 11:13
  • 2
    Welcome to Super User. Your question is very low quality. This is a Q & A site, so you need to ask a specific question. Please provide details of what you have attempted so far and where you have gotten stuck. Sorting and finding are two different things in Excel. Please explain what exactly you are trying to do.
    – CharlieRB
    Commented Jul 24, 2015 at 11:49
  • In my excel sheet there is one coloum with e-mail ids I want to find cells with special Character '@' two times. I want to find cells with two E-mail Ids. as I want to keep one e-mail id in one cell. Commented Jul 24, 2015 at 11:51
  • Your question comes across like " I have done no work, or research, I just want you guys to do my work for me" - I'm sure this isn't the case but it's best to show what you've done :) ... As it is, it is unclear what you want and it's too broad and will likely be closed...
    – Dave
    Commented Jul 24, 2015 at 11:58

2 Answers 2

3

I respect duDE's comment but I have this handy:

Add extra column with this formula. This assumes the first value is in A2

=LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))

You can then sort by this new column to give you the number of occurrences

6
  • Change the ; to ,. You might be using older Excel Commented Jul 24, 2015 at 12:24
  • It basically takes the length of the original text and minus the length of the text without the @ Commented Jul 24, 2015 at 12:26
  • ; didn't work in 2010 (which is what the OP has), updated your post to use , - IMO this is a better answer than mine, +1
    – Dave
    Commented Jul 24, 2015 at 12:26
  • 1
    In Excel 2010 you need to use , not ; Commented Jul 24, 2015 at 12:27
  • 1
    Yes Shaun, you need to use , and since the OP is using Excel 2010, the question should be for Excel 2010 :)
    – Dave
    Commented Jul 24, 2015 at 12:28
1

This will do it

Option Explicit
Sub Sigh()

Dim row As Integer
row = 1                        'UPDATE THIS FROM 1 TO THE FIRST ROW
Do While (Range("A" & row).Value <> "")

    Dim occurances As Integer
    occurances = StringCountOccurrences(Range("A" & row).Value, "@")
    If (occurances > 1) Then
        Range("B" & row).Value = "Yes"
    End If

row = row + 1
Loop

End Sub

Function StringCountOccurrences(strText As String, strFind As String, _
                                Optional lngCompare As VbCompareMethod) As Long
'copied from http://codevba.com/visual-basic-source-code/vb-string/count_occurrences_in_a_string.htm#.VbIjzyty3OQ
Dim lngPos As Long
Dim lngTemp As Long
Dim lngCount As Long
    If Len(strText) = 0 Then Exit Function
    If Len(strFind) = 0 Then Exit Function
    lngPos = 1
    Do
        lngPos = InStr(lngPos, strText, strFind, lngCompare)
        lngTemp = lngPos
        If lngPos > 0 Then
            lngCount = lngCount + 1
            lngPos = lngPos + Len(strFind)
        End If
    Loop Until lngPos = 0
    StringCountOccurrences = lngCount
End Function

Not the answer you're looking for? Browse other questions tagged .