LinkBack URL
About LinkBacks
Looking for formula to place text values in column A:C (color yellow) into desired columns (in green color). Thanks
See attached file.
Looking for formula to place text values in column A:C (color yellow) into desired columns (in green color). Thanks
See attached file.
Formula solution for this would most likely require many helper columns. Is that ok?
Or a VBA solution could do it as well.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Please Login or Register to view this content.
Bernie Deitrick
Excel MVP 2000-2010
JBeaucaire: Helper column is great. No need for VBA. Thanks.
Bernie Deitrick: Could you please help with using Helper Column? Thanks.
No - your delimiter between strings within one cell is not ideal for formulas. You would need a total of 11 more columns, with convoluted formulas that would be hard for me to write and for you to maintain. Just use my macro. It worked perfectly.
formula at D2 (array formula)
=IFERROR(INDEX($A2:$C2,SMALL(IF(LEN($A2:$C2),COLUMN($A2:$C2)),COLUMN(A1))),"")
copy cross and down
That does nothing to split the values in the doubled cells.
Ghozi Alkatiri : did not split the values in the doubled cells. Take a look for correction. Non-array formula (without SHIFT + ALT + ENTER) will also be welcome. Thanks.
I was well on the way for developing the formulas with 2 helpers columns, but the work I was doing was predicated on the DELIMITER being period+comma+space. All was well until I got to row 24 of your sample data, where I ran into the same string of characters (period+comma+space) being utilized within a single entry.
Since your delimiter string is not unique, a formula will break on rows like that.
Sorry. Your data is incompatible with your need.
Here is the crazy set of formulas I worked out, works for the sample data except for row24.
Last edited by JBeaucaire; 03-23-2020 at 06:56 PM.
I think the delimiters need to either , 1 or , 2 - seems like a year begins the second string.
Bernie Deitrick: Thanks for the VBA code. The code works like charm for the initial post request. Could you please take a look at the attached file and adjust your VBA code to achieve desired results.
Thanks.
JBeaucaire: wow....thank you very much for setting this up. I think VBA is my best option. The VBA code Bernie Deitrick proposed works, but I have attached a new file for him to take a look and adjust the VBA code to achieve desired results.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks