Google Sheets, remove duplicates in same cell

FullThrottle83

Newbie

Apr 19, 2021

24

7

• Aug 9, 2022

• #1

Hello!

Does anyone know how to remove duplicate entries in cells of Google Sheets?
I've scraped directories with Screaming Frog and it did a good job, but in the URL cells the addresses line up like: https://blackhatworld.comhttps://blackhatworld.com

I just want a single URL so it can be clicked.
Thankful for any assistance

 

Smackhead

Regular Member

Dec 22, 2021

391

222

• Aug 9, 2022

• #2

Use a left function something like =left(A1, Len(A1)/2)

Where A1 is the value containing duplicate. Take half of the length from the left. Might need to subtract 1 from the half.

I can write the exact formula if you havnt solved it by the time I get home.

 

FullThrottle83

Newbie

Apr 19, 2021

24

7

• Aug 9, 2022
• Thread Starter Thread Starter

• #3

Smackhead said:

Use a left function something like =left(A1, Len(A1)/2)

Where A1 is the value containing duplicate. Take half of the length from the left. Might need to subtract 1 from the half.

I can write the exact formula if you havnt solved it by the time I get home.

Click to expand...

You are very kind!

I really suck at Google Sheets. Can't get it working.
Done 195 cells manually removing the duplicate URL's..

Cells B196 to B1338 still have https://url.comhttps://url.com format

 

Smackhead

Regular Member

Dec 22, 2021

391

222

• Aug 10, 2022

• #4

Hmm what exactly is the issue ? Example attached.. maybe I'm missing something? If you want to pm me a link to the file I can update.

 

Attachments

• 
  Screenshot_2022-08-10-07-21-45-168_com.google.android.apps.docs.editors.sheets.jpg

  244 KB · Views: 106

FullThrottle83

Newbie

Apr 19, 2021

24

7

• Aug 10, 2022
• Thread Starter Thread Starter

• #5

Smackhead said:

Hmm what exactly is the issue ? Example attached.. maybe I'm missing something? If you want to pm me a link to the file I can update.

Click to expand...

You are very kind!
Unfortunately, I can't PM you since I haven't been active enough on this forum yet.

 

FullThrottle83

Newbie

Apr 19, 2021

24

7

• Aug 10, 2022
• Thread Starter Thread Starter

• #6

This seems to work:

=left(A1; LEN(A1)/2)

But now I have double URL's in cells A1-A2049 - so how do I grab them all to B1-B2049 for example..?
Sorry for bothering you - haven't touched excel/g-sheets in many years..

 

Smackhead

Regular Member

Dec 22, 2021

391

222

• Aug 10, 2022

• #7

Copy the formula, highlight cells b1- b2049 and press paste