Beginner here... I have a CSV file that requires 2 rounds of parsing, and I cannot figure out how to do it: 1) parse out rows that are all in one cell, currently separated by an Alt-Enter, into their own rows (and cells); 2) parse out comma-separated-values in these new rows into their own columns. Please refer to the attached JPGs of file as a guide to illustrate what I am trying to accomplish. There are 2 worksheets: “Incoming data” shows what I receive (in CSV format), and “Goal” shows where I need to end up (preferably in Excel format): 1. In “Incoming data” worksheet, column E (“Comments”) contains data that is on multiple lines separated by Alt-Enter, but within the same cell. Data in each row within the cell is separated by commas, always in the following order: GA123456 = invoice number (this alphanumeric value always starts with a GA, QR, QC, or AC. The number of numeric digits after the 2 letters is always 6, and they are always numbers) 7 = dispute reason (number can be anywhere between 1 and 25) $71.40 = disputed amount (sometimes with dollar sign, sometimes without. Value can be anywhere from $0.01 to $200000. Value will never be separated by a comma for $thousands) 2. Column E needs to be parsed out so that each row within cell E2 is in its own cell (and therefore row), and the data in columns A-D and F-I are duplicated to the new rows. 3. Column E then needs to be further parsed out so that each comma separated value is in its own cell, under its new column heading. See worksheet “Goal” in attached file to illustrate my final desired goal. Whether step 2 comes before 3 or vice versa, I don’t care. As long as the data ends up as per “Goal”, I am happy. Additional comments on the CSV data file: 1. The CSV file will be ultimately be used in MS Excel 2010 for further processing. 2. The CSV file will be up to 5000 rows long, but the number of columns will stay the same. 3. In the ‘Incoming Data’ Comments column, there could be up to 15 separate rows within one cell, each row separated by an Alt-Enter. This example only shows three. 4. The “Disputed Amount” will undoubtedly be sometimes entered with a $ sign, and sometimes without. I need the macro to work either way. 5. The parsed data as per the “Goal” worksheet should be in Excel format, if possible. Many thanks to any and all who choose to assist! Modsavant
... View more