An application I use exports strings of data in an array in one cell, like so:
"data1"= value1; "data2"= value2; "data3"= value3; "data4"= value4; "data5"= value5;
I'd like an equation or macro or something that could split it like this:
Is this possible?
Please Login or Register to view this content.
Excel 2003 on Windows XP
Last edited by devinpitcher; 06-08-2011 at 11:17 AM.
Hi,
Are the Value items numeric, alpha or a mixture?
How many cells are involved - presumably more than the single cell you mention above. And in which case how do you want the second and subsequent cells parsed. Do other cells have exactly the same 'data1', 'data2' items or do they have further unique values like 'data6', 'data7' etc which extends the table further to the right.
It would help if you could upload a representative example showing the before and after position.
Regards
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
This will do it of a single cell:Please Login or Register to view this content.
This is a better representation:
This is a sample of the data:Please Login or Register to view this content.
"search-terms1" = Chinese jade; "search-terms2" = Chinese archers; "search-terms3" = Antique jade; "search-terms4" = Asian jade; "search-terms5" = China history;
If possible, an equation would work better for this situation, but a macro could work, if it could do this to all rows in the database.
Please address Richard's post.
Are the Value items numeric, alpha or a mixture? Numbers and letters. No symbols as far as I know.
How many cells are involved? There are hundred of rows, and there will be a result of 5 columns per row. search-terms1 - search-terms5.
And in which case how do you want the second and subsequent cells parsed. I'm looking for the result to be 5 new columns, each with the data from the array.
Do other cells have exactly the same 'data1', 'data2' items or do they have further unique values like 'data6', 'data7' etc which extends the table further to the right. Just data1 - 5, which will actually be called search-terms1 - 5
Here is a database sample.
Results on sheet2:Please Login or Register to view this content.
Could you embed that into a excel document or something for me? I get an error when I try to run it.
Hi,
Try adding a -1 to the For Loop line in Stephen's code, i.e.
Please Login or Register to view this content.
I'm getting "Object required" for this line:
With Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
Hi,
Does
help?Please Login or Register to view this content.
Nope.
I just made a new Macro, pasted this in:
... and tried to run it. Did I forget something? Do I need to select something before running? Runtime error 424.Please Login or Register to view this content.
Hi,
Are you sure your data is on Sheet2? That's the VBA named Sheet2. This is not necessarily the "Sheet2" tab name or even the second sheet counting from the left in the Excel App.
Regards
I have Sheet1 and Sheet2. With the data on either 1 or 2, when I run it, I get that error.
Could somebody use the Template I uploaded and attach a working Macro to it possibly? I'm good with Excel, just never used Macros before.
Ok, I made a new document and tried again. It worked without an error, but all it did was copy the data over to Sheet2 in the same format.
Last edited by devinpitcher; 06-08-2011 at 01:48 PM.
Solved. I checked my post on Stack Overflow, and they came up with something that worked.
Last edited by devinpitcher; 06-09-2011 at 08:27 AM.
Hi All,
I have one queary ,In may collage 4Persons are got same marks as below.
Name University Markas Markas Plan
Nagaraju/Srinivas/Madu/Vijai Nu 25 30 40
I want to split these records as below .Is it possiable ,Please revert with code
Name University Markas Markas Plan
Nagaraju Nu 25 30 40
Srinivas Nu 25 30 40
Madu Nu 25 30 40
Vijai Nu 25 30 40
Thanks,
Nagaraju
Nagaraju,
Welcome to the Forum, 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.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I realize that this thread is over a year old, but nobody offered a formula solution, so here is one based on the workbook in post #8.Originally Posted by devinpitcher
An application I use exports strings of data in an array in one cell, like so:
"data1"= value1; "data2"= value2; "data3"= value3; "data4"= value4; "data5"= value5;
I'd like an equation or macro or something that could split it like this:
Is this possible?Please Login or Register to view this content.
Excel 2003 on Windows XP
It might be of use to others searching the forum.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks