LinkBack URL
About LinkBacks
Is there anyway to easily rearrange words in a single cell with say a drag and drop option or something easier than cut/paste?
Is there anyway to easily rearrange words in a single cell with say a drag and drop option or something easier than cut/paste?
In a random fashion or otherwise? I think you'll need to look at VBA for this - shall I move the thread to the right place for you?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Sure.
Like this:
Original Final
BURTBEE: BRIGHTENING EVEN SKIN TONE MOISTURIZING CREAM BURTBEE: MOISTURIZING CREAM BRIGHTENING EVEN SKIN TONE
BURTBEE: GUD ORANGE PETALOOZA NATURAL CLEANSING WIPES BURTBEE: CLEANSING WIPES GUD ORANGE PETALOOZA NATURAL
BURTBEE: SENSITIVE NIGHT CREAM BURTBEE: NIGHT CREAM SENSITIVE
CLIF: BUILDER'S VANILLA ALMOND PROTEIN BAR 6CT CLIF: PROTEIN BAR BUILDER'S VANILLA ALMOND 6CT
CLIF: COCONUT ALMOND CHOCOLATE WHEY PROTEIN BARS 5CT CLIF: PROTEIN BARS WHEY COCONUT ALMOND CHOCOLATE 5CT
CLIF: PEANUT BUTTER & CHOCOLATE WHEY PROTEIN BARS 5CT CLIF: PROTEIN BARS WHEY PEANUT BUTTER & CHOCOLATE 5CT
No, sorry - you've lost me completely ...
Trying in MS WordOriginally Posted by AliGW
No, sorry - you've lost me completely ...
Thanks
Like this...
I guess from your sample that the order matters?
Yes it's supposed to be "BRAND: NOUN VERBS ETC."
Excel is not natively clever enough to distinguish between parts of speech, so building a formula to tell it how would be impossible. I think even using VBA will be a monumental task, but I'll move you to that section of the forum and amend the thread title for you to see if anyone can help.
I believe just copying that column to Word and editing there "drag and drop" would prob be the simplest way to go....thank you though.
OK - well, if you decide you don't want any further help here, then please mark the thread as solved. Thanks!
I would think this is not just monumental, it would be impossible. Some of the words could be either nouns or verbs depending on the context. Others are "connected" by an ampersand (&) and so should be treated as a single word (noun).
How you would go from this "CLIF: PEANUT BUTTER & CHOCOLATE WHEY PROTEIN BARS 5CT" to this "CLIF: PROTEIN BARS WHEY PEANUT BUTTER & CHOCOLATE 5CT" is anyone's guess.
Last edited by TMS; 04-05-2017 at 12:27 PM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I thought so, Trevor, but thanks for confirming.
Yes; I wish that I could offer some encouragent but even something as simple as "tone" could be taken either way. And, according to Dictionary.com, "night" could be a noun, an adjective or part of an idiom, for example, "night and day".
I realise that the context is somewhat limited but my order of preference could be different to anyone else's ... and defining a set of rules, I suspect is beyond the capability of Excel.
I think its down to whether you can identify a common pattern/set of "rules". In your example, all but the last two examples seem to meet the following set of rules:
- The first word, including the colon, remains the first word.
- If the last word starts with a number, it stays at the end.
- The last two words, ignoring one starting with a number, go to the second position.
However in the last two examples, the position of the word WHEY doesn't meet those rules!
So ... if you can define a single set of rules, no matter how complex, it can be done with VBA. But if it requires human knowledge/interpretation, then it can't!
I suppose another option is if you create rules that would work for most, it would give you less results to manually amend... ?
not exactly what the OP asked for but a little tool that could help
since there is no "computerised" logic to rearrange the words
so the next best thing i can think of is specify the order you want of the words and let the computer combine it based on this
so the attached UDF takes this idea
first you would need to split the original cell by using text to column and using Space delimiter
the rest is somewhat self explanatory i hope
if there was a much longer list, it may be a little faster than the OP doing in word with drag and drop ?
at least certainly it would be faster to fix any errors in the arrangement of the words
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks