Hello all, first up very grateful to all who take time to help other. Many thanks.
I recieve huge quantities of data in a format that is not very easy to use for analysis, this is mainly because alot of the data is put in the titles! But these title have a consistant format, which i know can be used to pull them into seperate cells for analysis. I have included an example workbook. Any help would be greatly appreciated.
Many thanks
Alan
Hello Alan,
This macro will parse the data as you specified. If any of the parsing criterion fails then the table will be left blank. The macro is setup to run on the Active Sheet in the workbook. It currently starts at parsing at cell "A38". Change this starting cell to what you need.
Please Login or Register to view this content.
Last edited by Leith Ross; 05-04-2011 at 06:59 PM.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hello Leith Ross many thanks for the reply and the macro. Upon testing it, i found that whilst is split the first two patterns from the title cell into seperate cells. The third pattern and all the patterns after this were grouped together as one cell. I have attatched the spreadsheet on which it was trialed so you can see what i mean. Hope this is a simple modification.
Many thanks again.
Alan
Please Login or Register to view this content.
Last edited by snb; 05-05-2011 at 09:33 AM.
Hello SNB, thankyou for getting involved in this problem. If i try and run that code it gets stuck on "sn (4)." Also doe the "TT+" mean it is looking for that exact phrase? Because "TT+" is representative of text charecters and doesnt spefically mean "TT." (i may well have missunderstood.)
Many thanks again.
Alan
This oneliner suffices:
PS. I f the sample you post doesn't represent the data you are working with it's impossible to test any suggestion.Please Login or Register to view this content.
Wow thanks that is impressive, but there are still two things that it doesnt achieve. It currently doesnt remove the "Further text after this point but is not required"
Also in the comments i made about "Wanted text several words long" I meantioned that this may contain words which contain brackets around them. In the current form the code messes up upon encountering one of the bracketed words.
p.s.Appolagies if my spread confuses you at all,i do try my best to put across my problems in as representative fashion as possible.
I cant thank you enough for this help.
All the best Alan
In that case:
Which can be 'simplified' byPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by snb; 05-05-2011 at 10:54 AM.
Hello Alan,
I misunderstood your original post. When you said "number" in reference to this string "(#)", I took it to mean the pound sign would be replaced by a number. I made the necessary changes and it runs all the way through. Here is the updated macro. The attached workbook has the macro added already.
NOTE: If you still have problems, please post the ORIGINAL DATA.
Updated Parsing Macro
Please Login or Register to view this content.
Last edited by Leith Ross; 05-05-2011 at 12:07 PM. Reason: Added note
Ahaa brilliant this one was very close. Thanks so much for this help. This one does indeed work for the example workbook however if you replace (#) with a number as it si supposed to represent (5) then it doesnt work. It groups pattern 4 onwards as one cell.
Also as mentioned in the spreadsheet Wanted text several words long can contain brackets. So it needs to be able to cope with Wanted text several words (long)
As ive said before i cant thank the people responsible for the contributions in this thread enough.
Many Thanks Alan
Hello Alan,
Are the numbers just digits from 0 - 9 or are there spaces, decimals, or minus signs as well. What does the text inside the parentheses look lke?
Creating pattern matching code that works requires seeing the original data and as much of it as possible. Not posting the original data wastes time. This is why I made the request in my last post. If there is some extenuating circumstance preventing you from doing then let me know what it is.
Hello Leith Ross
PM sent and yes the numbers should always be in number format, no minus signs or anything else, it should also never be greater than 9 so no need to allow for two digets.
Many thanks
Alan
Hello all, this thread appears to have stagnated and i have been informed that this is due to my example lacking representative. Unfortuately for security perposes i cannot supply the real data. However this new data is 100% representative, in that i have simply replaced letters with other letters and numbers with other numbers. All formatting remains the same as my master document.
Please help if you can this, problem is proving to be a real pain.
All the best
Alan
Hi Alan,
Test and Post Back as you already know the drill...
This is not the best way of doing things but maybe JUST maybe it will work for you.
...
Thank You, Mike
Some Helpful Hints:
1. New members please read & follow the Forum Rules
2. Use Code Tags...Place[code]Before the first line and[/code] After the last line.
3. If you are pleased with a solution mark your post SOLVED.
4. Thank those who have help you by clicking the scales at the top right of the post.
Here...
Hello Alan,
Hopefully this will fulfill your requirements. It works on the data you provided. The real test will be when you run it on the actual data. The macro has been added to the attached workbook.
Please Login or Register to view this content.
Amazing guys! RNG5000, yours works completely for the most part, i tested in on hundereds of datasets (2days worth.) And returned with a 2 or 3 titles it doesnt seem to work for/returns debugging error for. I also had trouble implementing this code, since the function uses multiple macros, and the button supplied is very strange (to me) it is unmovable and uneditable? To see example for the debugging see "title splitter debug3" Many thanks!
Leith you coding is top notch as always and there is only one error i have encountered, and it occurs fairly regularly. After "Wordwor 7" there is some times some useless text that i need removed before the start of the next pattern "£3500". I assume it would be posible to end "Wordwor 7" after 'so many' characters? It is always a 7 letter word followed by a one/two digit number. To see what i mean in an example see the attatched "title splitter debug3" (Macro called "macrosplitterLR", not the buttons)
Many thanks to both of you!
Alan
Hello Alan,
You're saying this result is correct: "Texttex 15 "?
Are trailing spaces Okay or not?
This result is not correct: "Wordsss 4 Unwant edtexts = 11 (9) 11 (11) "?
This should be: "Wordsss 4", yes?
The rule is it must be 7 alphanumeric characters, no punctuation, followed by a single space, and 1 or 2 digits?
Yes everything you just said, apologies for not making this clearer
Hello Alan,
I changed Pattern(5) and it appears to be working correctly. Here is the macro code with the change in bold. The attached workbook has the change added also.
Please Login or Register to view this content.
Hello Leith Ross, macro works perfectly as far as i can tell, (tested it on a couple of days worth of data, with no errors! ) One thing i cant quite identify how to do is to change the offset, normally this is a simple case of changing a (2,0) to a (2,112) or similar, but in this macro i can find no obvious place that performs this function. I need to offset by 114 to the right, ending up in colomn DJ ? Have i simply missed the place to do this, or is it indeed more complex in this code?
Many thanks
Alan
Hello Alan,
If you want to start in column "DJ" instead of "A"...
Please Login or Register to view this content.
Hello Leith, i was actually looking for a way of changing where it pastes the first pattern. My datasets are in column A but i was hoping to be able to change an offset of where the resulting patterns go? Is this complicated? If so don't worry about it, since I can think of a way round it. I shall mark this thread as solved! I have given you the reputation boost deserved! Thank you so much for you help!
Best Regards
Alan
Hello Alan,
The offset is from column "A" (Rng value) to "DJ" on the active sheet. The offset is currently set to 4 (column "E") on the active sheet.
Please Login or Register to view this content.
Hi Leith, the code does work but I have identified two occasions where the data varies slightly. Sincerest apologies on having to come back to you again on this macro. It has proven the most complicated and difficult task to get solved.Mainly I guess due to the complexity of the task.
In the attached spreadsheet there are 5 titles that do not work properly. I believe these fall into two categories.
The first :
Demonstrates that similar to after the pattern "Wordsss 4" there may also be useless miscilanious data after the second patten Words shown in green. In the first two examples this shows its self as (D.I) AND (D.II) respectively.
The second:
Doesnt split maybe due to speach marks? Is it possible to make the Words shown in green be defined by what is before and what is after it. As this seems to be the most volatile section, as it is essentially titles. Including web addresses and seems to be able to contain anything. However it is always preceded by the pattern 2.10
and always ends at (5) (numbers are not correct and are simply an example)
I really hope you can help with this, as I really need it for my work. :S
Please see attached workbook.
All the best
Alan
Hello Alan,
After do some tests I have a problem with using the Regular Expressions. The problem is with multiple data enclosed in parentheses like this:
1.25 ""AGAIN SP-5"" EACHMAR' KSDUN (4) (5Nm +) 9M 6L - Wordwor 5 Wordwo Wordwor = 94 (8) 102 (49) £3700 Tober Movedagai Nwor -
While the Regular Expression should parse up to the (4) on the second second pattern (Patterns(1)). However, it is going to (8). It appears the Regular Expression is finding the last matching substring. This behaviour is supposed to be controlled by the Global property setting. After setting this to False, the results are sama and should not be. It appears to an undocumented feature, i.e. "bug". I will need a little more time to fashion a workaround for this new "feature". Thanks for your patience.
Hello Alan,
I rewrote the macro and tested it extensively. It passed with no problems. The actual data will be the final test. Here is the new macro code which has been added to the attached workbook.
Please Login or Register to view this content.
Hello Alan,
Disregard my last post. I left out one of the fields. This has all seven fields of the title. Here is the macro code and the workbook with the macro added.
Please Login or Register to view this content.
Hello Leith,
I have tested the macro on larger quantities of data now, and it has come back with one format (I think) that causes it to fail. This is when the pattern (5TT +) contains a "O" instead of a "+" i.e. (5TT O). I hope this is a relatively easy bug to fix. I found 27 titles that failed out of 500 or so. I then went back through and changed "O" to "+" and it fixed it with them all working, however we can't simply replace the "O" with a "+" as the O represents a parameter. In the attached spreadsheet i have included 3 more example datasets which fit this failing format. (These are listed after the highlighted row in the sheet)
Thanks so much for all the help
Alan
Last edited by ad9051; 05-31-2011 at 07:12 PM.
Hello Alan,
You were correct that it was a simple change. Pattern(2) was changed to this...
Please Login or Register to view this content.
Here is the complete macro code. The attached workbook contains the update.
Please Login or Register to view this content.
Thanks alot! I shall now mark this thread as solved (im hopeful that is the final touch required to finish this macro)
Many thanks
Alan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks