Viewer Q! 🙋♂️ How to Remove Duplicates from a Single Cell in Excel 🔥[EXCEL TIPS!]
ฝัง
- เผยแพร่เมื่อ 12 มิ.ย. 2023
- In this step by step tutorial we'll use several Excel functions to remove duplicate values from a single cell in Excel. We'll use the following functions (available in Microsoft 365): =TRIM, =TEXTJOIN, =UNIQUE, =TOCOL, =SORT, =IF, AND =COUNTIF.
We'll combine these in addition to the process I used for analyzing select all that apply survey data when those responses export into a single column. • How to Analyze Select-...
Thanks Kate for your #ViewerQuestion
💻 DOWNLOADS FOR PURCHASE
Zip Code Lookup Template [Excel] spotlightimpact.gumroad.com/l...
Square Carousel Template [PowerPoint] spotlightimpact.gumroad.com/l...
10 Minute Timer Slide Template [PowerPoint] spotlightimpact.gumroad.com/l...
Net Promoter Slider Chart Template [PowerPoint] spotlightimpact.gumroad.com/l...
Waffle Chart Template [Excel] spotlightimpact.gumroad.com/l...
Data Placemat or Dashboard Template [PowerPoint] spotlightimpact.gumroad.com/l...
🙏 SUPPORT MY CHANNEL
Subscribe to this channel for more tips, tricks, and tutorials! / @spotlightimpact
Join Spotlight Super Fans for early access to weekly tutorials / @spotlightimpact
Hit the THANKS button in any video!
Grab data design souvenirs from my shop on Gumroad spotlightimpact.gumroad.com/
Grab data design souvenirs from my Payhip shop payhip.com/spotlightimpact
Buy Spotlight Impact SWAG on Spring spotlightimpact.creator-sprin...
🎒 COURSES & TRAINING AFFILIATES
Depict Data Studio Courses depictdatastudio.teachable.co...
Excel University affiliate.excel-university.co...
Miss Excel (Opens monthly) links.miss-excel.com/exclusiv...
🙋FAQs
I’m using Microsoft 365 in my videos: PowerPoint, Excel, Word. If you don't have a feature you see here, check the Microsoft support pages for a version feature list
support.microsoft.com/en-us
Buying courses through affiliate links above costs no extra, but gives a small percentage of the proceeds to my channel.
#excel #exceltutorial #exceltips
![Viewer Q!🙋♂️ Remove Duplicates from a List in a Single Cell 🔥[EXCEL TIPS!]](http://i.ytimg.com/vi/cEWbaweH4NY/mqdefault.jpg)
![Viewer Q!🙋♂️ Remove Duplicates from a List in a Single Cell 🔥[EXCEL TIPS!]](/img/tr.png)
A bit long process, but works fine for me, thanks a lot for the tutorial 😉😉
It is long! I bet there’s an easier way with VBA I just don’t know how 🤷🏼 thanks for watching!
This tutorial was exactly what I needed for a project I'm working on! Thank you!!!!
I’m so glad it was useful! I hope you’ll share and subscribe 🙏🏻
Exactly what I needed thank you!!
Yay! I’m glad it’s useful! I hope you’ll subscribe and share 🙏🏻
What formula we have to apply if there is blank in between words and digits in cells for splitting that values
You’ll want to check out the =TRIM function for that.
What if, we only have one column and need to highlight those with the first 5 matching characters on the same column?
You'll likely want to use the LEFT function to first spit out a new column with the first five characters of your source colum www.customguide.com/excel/excel-left-function - after that though I'd have to sit with it more to figure out how to match or highlight. You might want look up the INDEX and MATCH functions, those could be of use here too.
this is only feasible if you have a relatively small data set, what if you are dealing with hundreds of unique data sets ? is there a video tutorial you can suggest?
I will do some sleuthing to see what I can find. I may also back into some of the Miss Excel Exeleraror course to see if she’s got any tricks about this one - it might involve creating some VBA behind the scenes. links.miss-excel.com/exclusive?ref=nick91
Thats what ive been looking for but the problem is it removes the first dupicate i would need the 2nd duplicate to be deleted 💀Good job tho most people only show the way to remove duplicate columns lol.
Thanks for watching!
my dataset has cells like this N2 = IndiaIndiaIndia, N3 = GermanyGermanyGermanyGermany, and further, the repetition of country names together isnt constant, some cells have more than 6 repetition. i want to know how i can solve it to make it come up once like N2 = India, N3 = Germany. I am trying to get each character and match, i think i need a for loop which is not in excel which am realizing it now while writing this comment.
That one's a tough one - do you have a full list of the unique countries used? My first thought might be to use the "LEFT" function which returns a set number of characters from a string of text. For call N2 with IndiaIndiaIndia in 02 you would type =LEFT(N2,5) and that would return "India". That will only work for countries with 5 characters in their name though there's still a lot of manual cleaning involved I think. I'll have to noodle on this one and see if there's a good solution to post. Thanks for the question.
You might also try using Flash Fill which will work for any country name that starts with a capital letter and only includes one word. th-cam.com/video/hKb6QLHjD8c/w-d-xo.html
@@SpotlightImpact woah ty for replying, ill look into it
@@SpotlightImpact ty for the help, ill keep on working on the solution, i just started to learn data cleaning
@@erolarmstrong you’re welcome. You got this 💪