How do you parse a time variable from database?

Reformatting Excel database files

• I have a very time consuming task of reformatting an Excel database with over 13700 rows. The rows ar formatted as follows: -------------------------------------------------- | First-Name | Last-Name | First-Name | Last-Name | --------------------------------------------------- | John & Mary | Doe | | | --------------------------------------------------- | James & Anne | Sanderson | | | --------------------------------------------------- I need to reformat the above example to read: -------------------------------------------------- | First-Name | Last-Name | First-Name | Last-Name | --------------------------------------------------- | John | Doe | Mary | Doe | --------------------------------------------------- | James | Sanderson | Anne | Sanderson | --------------------------------------------------- My first inclination was to write a program that would search for the '&' character, read the name that followed, and copy it into the second |first-name| field. It would then copy over the first |last-name| field over to the second |last-name| field. I first saved the Excel document as an XML file for to make it easier to parse through the fields. The program seems to reformat the database correctly, but Excel is unable to open the file, giving me this error message multiple times: XML ERROR in Table REASON: Bad Value FILE: C:\Documents and Settings\tyler\Desktop\fixed.xml GROUP: Row TAG: Cell ATTRIB: Index VALUE: 48 My question is this: Are there any existing programs or formulas available that can accomplish this task in Excel? If not, will you be willing to to read my source code and tell me my error? The program was written in C, and is not very long. If there is no solution other than writing a custom program, please respond with a request for the source code, that is, if you are willing to read it :) . Regards, Tyler

• Answer:

  Simoneau-ga, This VBA routine should do what you want. It looks at each used row in your spreadsheet. For each row, it looks at the value in the first column. If that value contains an & sign, it puts everything before the & in column 1, everything after the & in column 3, and copies the value in column 2 (the last name) into column 4. Beofre running the code, make sure you have a backup copy of your workbook. To run the code, open your workbook and go to the worksheet you need to reformat. Press Alt-F11 to get the VBA window. In the tree on the left side, you will see a list of your worksheets. Double-click on the worksheet you want to affect. Paste in the code below and press F5 to run it. That should reformat your worksheet as you describe. Public Sub DoIt() Dim vVal As String Dim vPos As Integer For Each rw In Me.UsedRange.Rows vVal = rw.Cells(1, 1).Value vPos = InStr(vVal, "&") If vPos > 0 Then rw.Cells(1, 1) = Trim(Left(vVal, vPos - 1)) rw.Cells(1, 3) = Trim(Mid(vVal, vPos + 1)) rw.Cells(1, 4) = rw.Cells(1, 2).Value End If Next End Sub Good luck with your Excel project! - Hammer Search strategy ---------------- None. Reproduced and solved problem in Excel.