Ask Your Question
0

Remove text within multiple cells [closed]

asked 2013-09-15 16:33:15 +0100

cyport gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-08 08:55:14.562082

2 Answers

Sort by » oldest newest most voted
1

answered 2013-09-15 18:07:26 +0100

JohnSUN gravatar image

updated 2013-09-15 18:09:28 +0100

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

edit flag offensive delete link more
0

answered 2013-09-15 18:14:48 +0100

David gravatar image

updated 2013-09-15 18:22:15 +0100

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)

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-09-15 16:33:15 +0100

Seen: 2,163 times

Last updated: Sep 15 '13