Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
I have been given a .csv file of several thousand rows, and I need to
change column A of each. The brief cutout below shows column A of the first two rows. (I need to skip the first row, which is the headers.) Kinda clumsy to have such a wide column, but the application this file is to be fed after I change the contents requires all the data separated by double-quotes to be in the one column. Cell A2: 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2 .. .. .. etc I need to do two things: 1. Add a constant to the 7-digit number which always appears after the first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes 1427386 (by adding constant of my choice in this case 10000). I need to apply this constant to column A of all rows. 2. Change the date which appears after the fifth double-quote to a new, constant date. 09/30/05 would become 10/14/05 for all rows. I don't care what date is in that position, I just want to write over it with the new one. Note that the number after the third quote can be variable in length (e.g., 307 vs 900000005). I suppose part of the solution could be to count (parse?) over five double-quotes to get to this date. All other data in the column remains the same. Here's the after-image of the above example: Cell A2: 09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2 TIA for any help you can provide. Ed |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing csv file all data is in first column | Excel Worksheet Functions | |||
how to put data in the column A to the next row of the column B | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Getting data from another excel file | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |