|
|
Subject:
Reformatting Excel database files
Category: Computers > Programming Asked by: simoneau-ga List Price: $30.00 |
Posted:
26 Aug 2005 15:44 PDT
Expires: 25 Sep 2005 15:44 PDT Question ID: 560956 |
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 | |
| |
| |
| |
|
|
Subject:
Re: Reformatting Excel database files
Answered By: hammer-ga on 27 Aug 2005 13:52 PDT Rated: |
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. | |
| |
|
simoneau-ga
rated this answer:
and gave an additional tip of:
$5.00
Excellent work Hammer. Thanks for the great script, it works perfectly. -Tyler |
|
Subject:
Re: Reformatting Excel database files
From: smallbusinessjim-ga on 30 Aug 2005 07:37 PDT |
Another option using some of the same code might be to create a custom function that could be reused. Function FirstName(name) vPos = InStr(name, "&") If vPos > 0 Then FirstName = Trim(Left(name, vPos - 1)) End Function Function LastName(name) vPos = InStr(name, "&") If vPos > 0 Then LastName = Trim(Mid(name, vPos + 1)) End Function Then if cell A1 contains "John & Sue", cell B1 contains "=FirstName(A1)" and cell C1 contains "=LastName(A1)", Cell B1 will return John and cell C1 will return "Sue". |
Subject:
Re: Reformatting Excel database files
From: myexpertsonline-ga on 30 Aug 2005 23:50 PDT |
hammer: Probably completely against the rules, right? But I'd love to hear from you. I'll wager you can find me. |
If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you. |
Search Google Answers for |
Google Home - Answers FAQ - Terms of Service - Privacy Policy |