This must be easy but I'm fighting with it for hours. Please help
I have lists of words like this one:
!BERLIN, !LONDON, !MADRID, !PARIS, !ROME, !ATHENS
I need a formula which will split these lines, each word in a separate cell, without the comas.
In the example attached, I need formulas in columns B-K, which will do this for up to 10 words for each row.
Thanks in advance!!
Last edited by Hitch75; 08-03-2012 at 01:03 PM.
Have you tried Text to Columns function under the Data tab
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
I need it to be automatic, with formulas...
Formulas get longer and longer...
B2
Formula:
Please Login or Register to view this content.
C2
Formula:Please Login or Register to view this content.
D2
Formula:Please Login or Register to view this content.
E2
Formula:Please Login or Register to view this content.
F2
Formula:Please Login or Register to view this content.
Hitch75,
Attached is a modified version of your example file. In cell B2 and copied over and down to cell K4 is this formula:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Here's a solution with just two formulas.
In B2 dragged down
same as Abousetta'sFormula:Please Login or Register to view this content.
In C2 dragged across and down
Formula:Please Login or Register to view this content.
See attached. Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Personally, I would use vba to split the cell into an array and print back to the sheet... but that's just me
Nice one Tiger.
abousetta, I took your formulas which get longer and longer
it works fine.
thanks!!
All, I am borrowing this thread as it very similar to what I am looking for except that I am looking to code a user defined function, "Split" with 1 argument. My text are delimited by | but with various parts and sometime empty part. Please help. Thanks.
Please Login or Register to view this content.
@ slc
Unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
no worry. I will create a new thread.
thought i should share this variation, entered in B2:
and drag-filled right...Please Login or Register to view this content.
Last edited by icestationzbra; 08-08-2012 at 11:17 PM. Reason: absoref
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Also to share with others on how to do similar text split using a user defined function. Refer to the URL below:
http://www.excelforum.com/excel-form...-function.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks