Need to extract all lines containing EA, EB, EC...EZ. (case sensitive)

Maxwell314 · #1 11-04-2015, 07:43 AM

Looking to extract all domain names containing EA, EB, EC, all the way to EZ (case sensitive). List has over 1k domains, sometimes much more.

Short Example:

List of all domains

EveryTime.com
EContest.com
EasyBuilder.com
EagleTech.com
EPlaylist.com
EEvents.com
EmeraldStore.com

What I need:

EContest.com
EPlaylist.com
EEvents.com

---

Now it wasn't hard to figure out a very slow way to do this - I am just in need of a faster way since I have to do it often.

If anyone knows how to do something like this any help is highly appreciated!

Maxwell314 · #2 11-05-2015, 07:40 AM

Anyone know? I would think this would be an easy solve for an expert...

NoSparks · #3 11-05-2015, 07:45 AM

Post a sample sheet with your slow way and we'll speed it up.

Maxwell314 · #4 11-05-2015, 07:59 AM

Quote:

Originally Posted by NoSparks

Post a sample sheet with your slow way and we'll speed it up.

Well part of it is done in excel then part is done with an online tool:

1) First I use find and replace: and I replace EA with -EA, EB with -EB, all the way to replacing EZ with -EZ. (case sensitive)

Basically just need to add the same symbol before every one. So things like EasyStreet.com won't have a dash (or whatever symbol) in front of it.

2) Then I use an online tool that let's me remove all lines not containing -.

Part one takes a bit and I have to do this many times sometimes...

Could there be a way to do this "find and replace" step in bulk?
Like all in one I could do
EA -> -EA
EB -> -EB
EC -> -EC
...EZ -> -EZ

NoSparks · #5 11-05-2015, 10:48 AM

Do you actually need the "-" ?
Or is that just so you can identify the rows ?

Maxwell314 · #6 11-05-2015, 11:04 AM

Quote:

Originally Posted by NoSparks

Do you actually need the "-" ?

No, I just put a dash there temporarily so I can then extract all the lines that contain it.

It doesn't even matter what symbol. (could be a "!,@,$, etc...). It's just added to the lines I do want so there is something that all the lines I want have in common - and therefore be used to be extracted.

So no it's not what I'm looking for in my end result or anything.

Once I do get all the ones I want in front of me, with a no longer needed dash in front of every one: I have another online tool that instantly gets rid of all the dashes. So that's not a problem.

NoSparks · #7 11-05-2015, 11:11 AM

Assuming your data is on sheet1 and
assuming your data starts on row 2 and
assuming your data is in column A and
assuming your data always starts with an upper case letter
(now you know why to post a sample sheet)
something like this should work.
Test it on a copy of your sheet.

Code:

Sub test()
    Dim str As String
    Dim i As Long
    
Application.ScreenUpdating = False
With Sheets("Sheet1")
    For i = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        With Cells(i, 1)
            str = .Value
            If Asc(Mid(str, 2, 1)) < 65 Or Asc(Mid(str, 2, 1)) > 90 Then
                Cells(i, 1).EntireRow.Delete
            End If
        End With
    Next i
End With
Application.ScreenUpdating = True
End Sub

Maxwell314 · #8 11-05-2015, 11:21 AM

Sorry I am a rookie, how do I apply this code?

NoSparks · #9 11-05-2015, 11:55 AM

And there's another reason to post a sample workbook.

Copy your sheet to a new workbook.
Close your original workbook.

With the new workbook, satisfy the assumption that were made, then:

Alt + F11 to bring up the Visual Basic for Applications Environment.
From menu bar click insert > Module
Paste the code into the pane that opens on the right.
Upper right corner X to close the VBA environment.
Save this workbook as a .xlsm (macro enabled file)

Alt + F8 brings up the Macro Dialogue box
Select the macro named test (will be the only one)
click run.

If that doesn't work, I'm afraid you'll need to break down and post a workbook.

PS: Please don't quote whole posts. Thanks

Debaser · #10 11-06-2015, 07:44 AM

If it's only the first two characters you want to check, and the data is in column A, in another column type:
=OR(EXACT(LEFT(A1,2),"E"&CHAR(64+ROW(INDIRECT("1:2 6")))))
and press Ctrl+Shift+Enter and then fill down. Now filter that column for TRUE, and delete the visible rows. (or filter for FALSE and copy visible data to a new sheet)

NoSparks · #11 11-06-2015, 08:45 AM

Nice one Debaser. I like that.

I don't know formulas, but I don't think 26 should have a space in it.

Debaser · #12 11-06-2015, 08:52 AM

It didn't when I posted it!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Why are message header rules are case sensitive	delboy	Outlook	1	10-10-2023 11:30 AM
Help with Case and Select case	brent chadwick	Word VBA	34	10-18-2015 02:13 PM
Deleting grid lines but keeping the axis lines	CoffeeNut	Excel	0	04-01-2013 01:50 PM
Case Sensitive (contains) Selection	apolloman	Excel	2	07-12-2011 04:50 AM
From all UPPER CASE to Proper Case	davers	Word	1	04-30-2009 12:41 PM