CODEWITHSUNDEEP
excelgoogle-sheets
sam
sam

Reputation: 10084

Remove www. from domains in excel

Ive got a list of urls in column A, some of them have www. some dont, (none of them have http:// in this example)

What im trying to do is strip the www. do ive got a list of domains that i can order alphabeticly. I was looking at using =RIGHT(A1, LEN(A1)-4) but not all the domains have www. Any idea how you would be able to do this using a formula, rather than VBA so that it can run in both excel and Google Drive ?

EDIT

Ive now updated the example spreadsheet with Alex Szabó's answer - https://docs.google.com/spreadsheets/d/1x-zjaIh7xpGeyWPbYLAn5RyNjbrDWFQdI_U5WmS3fys/edit#gid=0

Upvotes: 4

Views: 9109

Answers (5)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

You can just use the substitute function, if the string doesnt have www. it still returns the original string by default

=SUBSTITUTE(A1,"www.","")

Upvotes: 0

bledsoeut
bledsoeut

Reputation: 11

I think this might be really easy to fix with a basic find and replace as follows:

Find: www. Leave Replace blank.

Upvotes: 1

Alex Szabo
Alex Szabo

Reputation: 3276

You could check for the "www" in the beginning, and if it's there, then strip it by using all letters from the address, except the first 4.

=IF(LEFT(A2,4)="www.", RIGHT(A2, LEN(A2)-4), A2)

Upvotes: 4

JPV
JPV

Reputation: 27262

In addition to previous contribution, in a google spreadsheet another option would be

=ArrayFormula(iferror(REGEXEXTRACT(A2:A5, "^www.(.+)$"),A2:A5))

This formula references all data in col A and will create an output in one column (no need to drag down).

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Use an if condition to check for www. as the first 4 characters, and only take the right - 4 it it is...

=IF(LEFT(A1,4)="www.",RIGHT(A1,LEN(A1)-4),A1)

enter image description here

Upvotes: 1

Related Questions