Hello All,
I am using Excel 2010 and have the problem shown in the attached sample data.
The attached sheet shows a single row but the actual data is several thousand rows
'Before' Sheet shows values in Col C. There are total 12 text values separated by commas in the column but for explanation I have numbered them.
After Sheet shows the result desired through a formula if possible. If not then a VBA
TIA
Rashid Khan
Last edited by prkhan56; 12-19-2015 at 01:34 PM.
What is the logic for the split?
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
To upload in our system
No, I asked what is the logic for the SPLIT? How do I know what has to go in each column? What should go in A? Is it always the first 4 numbers? When and how do I determine where I need just one number from the string? You need to explain very clearly how you arrive at your expected outcome.
I will explain here in more detail.
The data is text data separated by commas.
First four text values, which I have written as numbers for explanation purpose only, will go in column A.
The complete 12 text values will remain in Col B.
The 6th value should go in Col C
and the 5th value in Col D
Hope this is clear now
Will there always be 12 values?
Yes it will be text values separated by commas
Enter this formula in A1 on your "After" sheet and pull formula to the right
Formula:
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Actually, I think that the above formula may not work well if instead of 1,2,3 you will have something else
Give this one a try instead.
Formula:Please Login or Register to view this content.
Okay, not sure if this would work for you.
1. I just copied the data from C to B (seemed a heck of a lot easier)
2. In A1 copied down=LEFT(B1, FIND("^",SUBSTITUTE(B1,",","^",4))-1)3. In C1 copied down=MID(B1, FIND("^", SUBSTITUTE(B1, ",", "^",5))+1, FIND("^", SUBSTITUTE(B1, ",", "^",6))-FIND("^", SUBSTITUTE(B1, ",", "^",5))-1)4. In D1 copied down=MID(B1, FIND("^", SUBSTITUTE(B1, ",", "^",4))+1, FIND("^", SUBSTITUTE(B1, ",", "^",5))-FIND("^", SUBSTITUTE(B1, ",", "^",4))-1)
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
Great solution from great masters.
All working fine
Thanks to everyone for their time and help.
Post marked Solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks