Remove part of text from all columns in an excel file

Question

I have an excel file that has a column that displays a web source URL. The data looks like this....

http://carter.mydomain.com/page1

http://expert.mydomain.com/page4

http://10629.mydomain.com/sample

http://genius.mydomain.com/form-1

etc

What I need to do is remove everything before and including the (http://) and everything after the first (.) So in the previous examples, I want to be left with the following data in the columns

carter

expert

10629

genius

Thanks in advance for any help with this. Karen

score 0 · Answer 1 · answered Oct 17 '14 at 14:57

0

you can use a combination of formulas such as this. This is assuming that A1 contains the first line with Carter in it.

=MID(A1,FIND("//",A1)+2,FIND(".",A1,FIND("//",A1))-FIND("//",A1)-2)

answered Oct 17 '14 at 14:57

Sorceri

7,870
1
29
38

score 0 · Answer 2 · edited May 23 '17 at 10:33

Here's a brute-force macro using regular expression search/replace which unfortunately is not automatically part of the search/replace function built-in to Excel. It would be more useful to prompt for the search/replace patterns instead of hardcoding, but you'll get the idea as this is just an example of using regular expressions. Note you may have to add the regex reference to your workbook first.

To use, select the range, then run the macro after adding it.

Sub SearchReplaceRegex()
  Dim reg As New RegExp  ' regex object
  Dim searchPattern1     ' look for this
  Dim searchPattern2
  Dim replacePattern1    ' replace with this
  Dim replacePattern2
  Dim matchCell As Range ' selected cell

  ' Set search/replace patterns
  searchPattern1 = "^http://" ' http:// at the beginning of the line
  searchPattern2 = "\..*$"    ' 1st period followed by anything until the end of the line
  replacePattern1 = ""        ' replace with nothing
  replacePattern2 = ""

  ' regex object settings
  reg.IgnoreCase = True
  reg.MultiLine = False

  ' Loop through each selected cell
  For Each matchCell In Selection

    ' Does it match the first pattern?
    reg.Pattern = searchPattern1
    If reg.Test(matchCell) Then
      ' If so, replace the matched search pattern with the replace pattern
      matchCell = reg.Replace(matchCell, replacePattern1)
    End If

    ' Change to the second pattern and test again
    reg.Pattern = searchPattern2
    If reg.Test(matchCell) Then
      matchCell = reg.Replace(matchCell, replacePattern2)
    End If

  Next

End Sub

For more info, see this post which has a ton of good info: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

you can just use the Replace feature in excel and use Find what:.mydomain.com/* and replace it with and empty string and then do another find what:http:// and again just replace with an empty string, no need for all the vba — Sorceri, Oct 17 '14 at 15:42
So true, Grasshopper, but then you wouldn't have been exposed to the power of the regular expression! :-) — Gary_W, Oct 17 '14 at 15:51

Remove part of text from all columns in an excel file

2 Answers2