LinkBack URL
About LinkBacks
Hi Excel Gurus, i wonder if it possible to extract unique words from sentence into column?
How to extract unique words from sentence into column.jpg
How to extract unique words from sentence into column?
Hi Excel Gurus, i wonder if it possible to extract unique words from sentence into column?
How to extract unique words from sentence into column.jpg
How did you define your unique words?
Regards,
Thangavel D
Appreciate the help? CLICK *
not repeating words,
A UDF perhaps
- test in attached workbook
formula in B2 copied down
=UniqueWordsOnly(A$2:A$11,ROW()-1)
Note
- the 2nd attribute should equate to 1 in the first cell
(example if the first value is to appear in row 3, amend ROW()-1 to ROW()-2)
- the range must be absolute
(use $ to lock in row numbers)
Sample result in attached workbook
UniqueWords.jpg
.
The UDF
- most punctuation marks are removed
- strings converted to lower case
Please Login or Register to view this content.
Last edited by kev_; 03-20-2018 at 08:53 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
This is quite simple, using Power Query (Get & Transform Data)
See attached workbook for worked example.Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
@Olly
Your PowerQuery option seems the most efficient solution
- is it possible to refine it so that words are not duplicated if followed by punctuation marks?
In this example "help" is listed 4 times:
Olly.jpg
Last edited by kev_; 03-20-2018 at 09:58 AM.
Yep - little bit more complex, but here's one way:
With a nod to Chris Webb: https://blog.crossjoin.co.uk/2014/08...n-power-query/Please Login or Register to view this content.
I've added a couple of extra tweaks, so it counts the maximum number of words before splitting into columns (to improve performance), and handles multiple spaces (for example, where source contains "this - value").
@Olly Thanks - superb
Thank you for your answer, i should learn UDF from you!
Olly, could You recommend a source to learn better Power Query ? )
Thanks for the rep
If we have answered your original question .. please mark thread SOLVED (ThreadTools@TopOfThread)
thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks