posted on January 10, 2004 08:08:49 PM new
I am using the archived sales records to do my taxes, and I want to pull out the credit amounts. But in the credit column the amount is combined with the payment type. Does anyone know how to use Excel to create a new field with just the dollar amount?
posted on January 11, 2004 04:03:02 PM new
Try this:
1. load the vendio archive into excel. Let's say this puts the "Credits" column at AI
2. insert a new column, let's say it's AJ
3. put this formula in cell AI2: (I'm assuming that your row 1 contains the column names)
=IF(LEN(AI2)>0,LEFT(AI2,SEARCH(" ",AI2)),"" )
4. now fill down
Your new column AJ should have dollar amounts for the items that sold, empty for items that have no credits.
[edited to try to avoid conversion of the last part of the formula into a smiley-face! ]
[ edited by gaugagaug on Jan 11, 2004 04:05 PM ]
posted on January 11, 2004 05:18:33 PM new
Thanks gaugagau! I need to get an excel book! I changed your If statement just a little bit to this:
=IF(LEN(I2)>0,VALUE(LEFT(I2,SEARCH(" ",I2))),0 )
So that I can calculate a total on that column. (I2 being the column that the Credit character string is in). Thanks a bunch!!
Anita
