I have column A1 through A100 each with 3 to 5 words in them, all of varying character lengths. What formulas could I use to accomplish separating those into single words over cells B1 through F1? It seems it would be some combination of LEN, SEARCH, TRIM, RIGHT, LEFT, etc, but I can't wrap my head around it exactly. See attached image for example.
I'm trying to determine formulas that could go into B1 through F1 and be filled down and would work for all strings in column A.
Thanks.
Stay of the active cell en run this macro
Please Login or Register to view this content.
Try this...
Data Range
This formula entered in B1:
=TRIM(MID(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",255)),COLUMNS($B1:B1)*255,255))
Copy down as needed then across until you get a column full of blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony, fantastic!! That works perfectly. Thanks so much!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks