Home Library Mail Merge question

Sharp10r

I have home library software (Readerware- it rocks) and I've exported the database so I can make spine labels in MS Word 2007. I've gotten to a good point, but is there any way to truncate a field? For instance, in the database, I want the author name to just be the first three letters. How do I tell MS word to do this? Or do I have to do it to the DB in excel? Thanks.

LaOs

Sharp10r wrote: »

I have home library software (Readerware- it rocks) and I've exported the database so I can make spine labels in MS Word 2007. I've gotten to a good point, but is there any way to truncate a field? For instance, in the database, I want the author name to just be the first three letters. How do I tell MS word to do this? Or do I have to do it to the DB in excel? Thanks.

I would probably just do it in Excel myself, since that'd be way easier for me than trying to figure out how to make Word do it. Is the exported database usable in Excel?

Sharp10r

yes it's usable. So there's a way to truncate the field in excel? I'm NOT going to do it for all 2000 records manually.

LaOs

Sharp10r wrote: »

yes it's usable. So there's a way to truncate the field in excel? I'm NOT going to do it for all 2000 records manually.

Oh, it's incredibly simple in Excel.

=LEFT(A1,3)

That formula will grab only the first 3 characters from cell A1, starting from the very left of the cell. You can change the cell reference and the number of characters however you wish.

Enter this formula on the same line in a new column (I would insert a new column at the very left of your original data) and then Fill Down to the bottom row that contains data. I would add the column to the left of your data so it's easy to see that everything worked right. Give the new column a Header like "Trunc" or whatever so you will easily identify it when picking the Field in the Mail Merge in Word.

So, if you have a Header row, and your original information, your first "data" cell you want to pull is A2. Insert the new column for the truncation, and then your first cell is B2. In A2, enter the formula: =LEFT(B2,3) Then highlight from A2 all the way down to the last row with data. Press Ctrl+d and you will Fill Down that formula for each row, automatically updating the formula for whatever row it's on. (B3, B4, B5, etc.)

Then, when you go to your Mail Merge, just use the new Truncated column instead of the original piece of information that was too long before. Let me know if you have any mroe troubles.

Sharp10r

LaOs wrote: »

Sharp10r wrote: »

yes it's usable. So there's a way to truncate the field in excel? I'm NOT going to do it for all 2000 records manually.

Oh, it's incredibly simple in Excel.

=LEFT(A1,3)

That formula will grab only the first 3 characters from cell A1, starting from the very left of the cell. You can change the cell reference and the number of characters however you wish.

Enter this formula on the same line in a new column (I would insert a new column at the very left of your original data) and then Fill Down to the bottom row that contains data. I would add the column to the left of your data so it's easy to see that everything worked right. Give the new column a Header like "Trunc" or whatever so you will easily identify it when picking the Field in the Mail Merge in Word.

So, if you have a Header row, and your original information, your first "data" cell you want to pull is A2. Insert the new column for the truncation, and then your first cell is B2. In A2, enter the formula: =LEFT(B2,3) Then highlight from A2 all the way down to the last row with data. Press Ctrl+d and you will Fill Down that formula for each row, automatically updating the formula for whatever row it's on. (B3, B4, B5, etc.)

Then, when you go to your Mail Merge, just use the new Truncated column instead of the original piece of information that was too long before. Let me know if you have any mroe troubles.

Wow! Fantastic. Thanks so much. That's exactly what I needed. I may PM you later if Ineed more help massaging the data in my DEWEY field... the 2 sites Readerware pulls the Dewey Dec. numbers from have different formatting... Thanks again!