Remove text within multiple cells

[cyport]

Hi,

I have a column containing names of towns, each cell has the name of the town and a .html link. I would like to remove all the .html links. Is there an easy way to do this? So far I know that I can remove “.html” from all cells but the rest of the link won’t be deleted.

An example of the column:

• Aberdeen aberdeen.html
• Aberdeen Queens Links aberdeen.html
• Aberdeen Union Square aberdeen.html
• Accrington accrington.html
• Aldershot woking.html
• Altrincham altrincham.html
• Andover salisbury.html

I am trying to delete the “aberdeen.html”, “accrington.html”, “working.html”, and so on, part of the cell.

Thanks!

[JohnSUN]

I think you mean the search string [:blank:][^[:blank:]]*.html with Regular expressions “On”

[dajare]

Another option: assuming “Aberdeen aberdeen.html” is in A1, copy this function into B1:

=MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))))

Then select B1, and drag the handle at the lower right corner down as far as your data goes. The B column should be the data listing you’re after. This is slower/clumsier than @JohnSUN’s regex search-replace, but perhaps useful depending on what you want to do with your data.

(Credit: adjusted from StackOverflow for this use case)