Parse cell data into multiple columns

Hello,

Can someone pls. assist me in providing me with vba code (or formulas) that can parse data from individual cells into multiple columns? I have attached a file noting the current inputs in columns A-F and the desired outputs in columns I-O. Additionally, I have noted the distinguishing criteria for each of the fields in cells I13-O13. Also, I noticed that each of the data points in the input cells are separated by at least two spaces. Hopefully, this will help in building the code. Lastly, the input data in the working sheet goes from columns A-H and the desired outputs would start from column I; with row 1 having the headers.

Thank you in advance.

Hello,

Any savvy VBA programmers able to address this inquiry? I would really appreciate the assistance.

Thanks

Hi maldonadocj ,

Thank you for providing a most interesting problem. I originally thought I would be able to handle your question using ordinary techniques, but your data patterns required some additional help from 'Regular Expressions'. 'Regular Expressions' allow finding, matching, modifying complicated data patterns using a series of codes (e.g. '[1-3]' matches 1,2, or 3 and '[0-1][0-9]' matches digits '00 thru 19'). There is an excellent 'Regular Expressions' sample Excel file (Thank you 'brettdj') from about ten years ago at
http://www.vbaexpress.com/kb/getarticle.php?kb_id=68

There is a pretty good tutorial at:
http://www.codeproject.com/Articles/...Regex-Tutorial

Please see Sheet 'Main' in the attached file. There are 4 Yellow command buttons:
a. To Clear Results Data
b. To Generate Results from a copy of your sample data
c. To Clear the 'Token' testing results area
d. To Test potential 'tokens' to see if they are recognized as one of your 'data types'.

I was unsuccessful in resolving the fields in your last data line that contained three line items on one line, because there seemed to be some 'ambiguous data'.

Please Login or Register  to view this content.

I also assumed on that 'long data line', that each 'virtual' new line started with type 'Name', otherwise I would have been completely lost.

Please let me know how you make out, and if you have any questions. I will be more than happy to make changes to suit your needs.

Lewis

The following is working 'Regular Expression' sample code using 'late binding' (i.e. no additional libraries or resources required) .

Please Login or Register  to view this content.

Hello Lewis,

Thank you very much! I'll review the code and let you know how it went.

Best regards,
maldonadocj

Hello Lewis,

Thanks again for providing the code and the informational code site. After testing the code (and my own rudimentary revisions based on the site you referred me to) on a couple of examples, I noticed that there were several more enhancements that are needed. The most important criteria to note is that the data that is being parsed out in each individual excel cell are email message bodies. Accordingly, there is going to be additional data in the email that is not necessary (headers, disclaimers etc) and that appears to hinder the code from running correctly. Additionally, would you be able to add details regarding the original sender and the send date in separate columns in the output? Lastly, in your file you noted "ambiguous data" in several cells. This data would need to be parsed out as well given that the string of data contains multiple distinct entries that would go on separate rows.

I have attached a revised file for your purview. I highlighted the additional criteria needed in the code in the "Guidance" tab from cell I13-O19. Additionally, I provided a consolidated table of what the "good data" arrangement would look like in the "Consolidated" tab from cells I25 - O44; with the input examples in cells G27-G29.

Can you pls. review the revised file and make the requested code changes? I appreciate your time and assistance with this matter.

Best regards,
Carlos

Hello Lewis,

I received your message. Pls. feel free to address this inquiry at your convenience. As always, much appreciated for the assistance.

Best regards,
Carlos

See if the attached is what you wanted

Hello Jindon,

Thank you for the assistance. I tested your code on a couple of examples and it did not appear to work correctly. As I mentioned to Lewis, I believe the code is not working due to the cells containing email message bodies that contain additional and unnecessary information. Accordingly, can you pls. review the revised file (Parsing Sample v2.xls) I sent to Lewis yesterday and determine whether your code can be revised to capture the "good data" points that I noted in the Consolidated tab as well as in the response I sent to Lewis?

I appreciate your assistance in this matter.

Best regards,
Carlos

Hello Lewis,

Did you have an opportunity to review the revised file? Pls. let me know if I can be of further assistance.

Best regards,
Carlos

Hi Carlos,

I'm actively working on your revised file.

I've been able to complete and thoroughly test:
a. E-mail Sender Name
b. E-mail Send Date
c. Id
d. Lag

The following are in progress:
e. Name (95% completed)
f. Rating
g. Price
h. Level
i. Lot

The problem I'm having is that since most of your input is free form text (which is fine), I want to make sure I identify each item properly and put the data in the correct column. Now that I understand what each item stands for, it makes the job a lot easier.

Some of the items are ambiguous if taken by themselves, and not in context.

For example in the following string your 'ideal output' has '96H' as a LEVEL,
789123AA2 MOMS 2001-GD14 A5A | Aaa/--/AAA 2.8 | 96H 5x --

but, in the following string '85H' is a price (because it is preceded by 'Traded'):
x $5mm 123456PAF5 DEFG 2006-LDX7 AU Traded 85H N+83 / N+78

There are several similar issues I am trying to resolve, which takes time.

There are two things that you could do for me:
1. In your sample file the 3rd e-mail had the first 6 data lines with 'Cusip Id' values that had 10 characters. Please verify whether this is correct or if it is an error.
2. I could use additional data for testing. If you could post a file that contains 3 to 10 e-mails it would be appreciated. I don't need the ideal output, just the raw e-mails.

Lewis

Hello Lewis,

Thank you very much. I'll start putting together the data you requested and will revert back to you asap.

Best regards,
Carlos

Hello Lewis,

Apologies for not responding sooner to your inquiries but I have been quite occupied at work. I will respond once things calm down at work; which I expect by early next week.

Regarding your question about the count of characters for cusips, it should be a total of nine and not ten. Thanks for catching that nuance and I appreciate your continued assistance and patience with this inquiry.

Best regards,
Carlos

Hi Carlos,

No apologies are necessary, I fully understand the sometimes unreasonable demands of work. Attached find the completed second attempt to solve your problem. I resolved all the issues I had with your sample data except one, which is described below. Since the sample data I have is limited, my speculation is that there will be several new anomalies recognized when the software is tested with an expanded sample of e-mail data.

Anomalies and/or Ambiguities:
1. '96H' is defined as 'Price', but in the Ideal output for the 'MOMS' line ( 2nd data line in the first e-mail) it is in the 'Level' column.
2. Some Id Numbers had 10 digits. The e-mail was edited to reduce the number of digits to 9.


Suggestions for formatting e-mails (if possible):
The following suggestions may improve perfomance (i.e. reduce errors):
a. Have at least two spaces between different items.
b. Items that are part of the same field (.e.g. Name) should not be separated by more than one space.


How the software works:
E-mails MUST be in Column 'G' starting at cell 'G2' on sheet 'Main'.
The following items can be changed by modifying a constant at the top of 'ModParse':
a. Sheet Name
b. Starting Cell (included column containing the -emails)
c. Last row cleared before start of processing (initially set to row 30)
d. Columns used for output data items

Please Login or Register  to view this content.

1. Each line in an e-mail is treated as a separate 'data line'.
2. Each 'data line' is scanned for key words (e.g. to indicate e-mail sender or e-mail send date).
Other key words indicate a line should be excluded from processing (e.g. 'Reserves Apply').
3. If a 'data line' is NOT EXCLUDED, then it is processed.

4. Before a 'data line' is processed, certain additional 'key words' in the 'data line' are removed (e.g. 'Buying').
This is to speed further processing and to prevent FALSELY identifying a keyword as ACTUAL DATA.
5. Each processed 'data line' is separated into 'tokens'.
A 'token' is a series of characters separated by no more than one space (in most cases).
Occassionally 'special processing' will be done to identify 'data itmes' that are separated by one space.

6. Each 'token' is evaluated from left to right with a special set of rules for each data item,
to determine if the token is one of:
a. Name
b. Id
c. Lag
d. Rating
e. Price
f. Level
g. Lot
h. None of the above

7. Occassionally a token will meet the criteria for more than one 'data item'. An additional
set of rules is applied to determine which 'data item' the token PROBABLY belongs to.

NOTE: All items in Sheet 'Main' on or below row 35 CAN BE DELETED.

Lewis

Hi Lewis,

Thank you very much for the revised code. i'll review it by Wednesday and let you know how it worked.

Best regards,
Carlos