I'm importing a text file into Excel 2003 and by using the text to data
facility I'm almost able to create a 'financial' spreadsheet. The problem is
that there is a column that contains entries that are credits and appear like
1062.22cr
I want the figure to be a true number but cannot find a way to format the
column. I'm not bothered whether it appears as -1062.22 or 1062.22- or even
1062.22. I just need to have it have the ability to recognise the numbers and
to calculate. To have it recognised as a credit would be preferable so that
the column could be used in conjunction with the debit col to have a running
balance.
Thanks

Hello Arceedee.

If your credits are in column A the:

=("-"&LEFT(A1,(FIND("c",A1)-1)))*1

Judith
--
Hope this helps


"Arceedee" wrote:

I'm importing a text file into Excel 2003 and by using the text to data
facility I'm almost able to create a 'financial' spreadsheet. The problem is
that there is a column that contains entries that are credits and appear like
1062.22cr
I want the figure to be a true number but cannot find a way to format the
column. I'm not bothered whether it appears as -1062.22 or 1062.22- or even
1062.22. I just need to have it have the ability to recognise the numbers and
to calculate. To have it recognised as a credit would be preferable so that
the column could be used in conjunction with the debit col to have a running
balance.
Thanks

Hi Arceedee,

Do I understand from your quote "To have it recognised as a credit would be
preferable so that the column could be used in conjunction with the debit
col" means that you have both positive and negative (cr) in the same column?
If so, then the following will convert all the cr to negative and all the
ones without cr to positive.

Assume that the data 1062.22cr is in A2 for the fomula. Insert a helper
column to the right of the column for the formula. Note that the formula is
all one line although it breaks in this post.

=IF(IF(ISERROR(SEARCH("cr",A2,1)),0,SEARCH("cr",A2 ,1)-1)0,-VALUE(LEFT(A2,IF(ISERROR(SEARCH("cr",A2,1)),0,SEAR CH("cr",A2,1)-1))),VALUE(A2))

Now that you have the numbers as positive and negative, you can custom
format with the following custom format for the column.

0.00;0.00"cr"

The part before the semicolon is the positive formatting and the part after
the semicolon is the negative formatting.

The easy way to transfer the above formula to your worksheet is to go to any
blank worksheet and in cell A2 insert one of your numbers. Copy the above
formula and paste it into cell B2. Now on you main worksheet, insert a column
to the right of your column of data and simply select cell B2 on the blank
worksheet and copy it and paste it into the cell to the right of your data.
Excel will fix up the cell addressing for you.


--
Regards,

OssieMac


"JudithJubilee" wrote:

Hello Arceedee.

If your credits are in column A the:

=("-"&LEFT(A1,(FIND("c",A1)-1)))*1

Judith
--
Hope this helps


"Arceedee" wrote:

I'm importing a text file into Excel 2003 and by using the text to data
facility I'm almost able to create a 'financial' spreadsheet. The problem is
that there is a column that contains entries that are credits and appear like
1062.22cr
I want the figure to be a true number but cannot find a way to format the
column. I'm not bothered whether it appears as -1062.22 or 1062.22- or even
1062.22. I just need to have it have the ability to recognise the numbers and
to calculate. To have it recognised as a credit would be preferable so that
the column could be used in conjunction with the debit col to have a running
balance.
Thanks

=IF(RIGHT(A1,2)="cr",-LEFT(A1,LEN(A1)-2),A1)
--
David Biddulph

"Arceedee" wrote in message
...
I'm importing a text file into Excel 2003 and by using the text to data
facility I'm almost able to create a 'financial' spreadsheet. The problem
is
that there is a column that contains entries that are credits and appear
like
1062.22cr
I want the figure to be a true number but cannot find a way to format the
column. I'm not bothered whether it appears as -1062.22 or 1062.22- or
even
1062.22. I just need to have it have the ability to recognise the numbers
and
to calculate. To have it recognised as a credit would be preferable so
that
the column could be used in conjunction with the debit col to have a
running
balance.
Thanks

Thanks to all for your suggestions. Judith was first to reply so wins the
virtual prize. Cheers. Arceedee.

"Arceedee" wrote:

I'm importing a text file into Excel 2003 and by using the text to data
facility I'm almost able to create a 'financial' spreadsheet. The problem is
that there is a column that contains entries that are credits and appear like
1062.22cr
I want the figure to be a true number but cannot find a way to format the
column. I'm not bothered whether it appears as -1062.22 or 1062.22- or even
1062.22. I just need to have it have the ability to recognise the numbers and
to calculate. To have it recognised as a credit would be preferable so that
the column could be used in conjunction with the debit col to have a running
balance.
Thanks

OssieMac gets the real prize however for the one that works best with my
application. Thanks again to all. Arceedee.

"OssieMac" wrote:

Hi Arceedee,

Do I understand from your quote "To have it recognised as a credit would be
preferable so that the column could be used in conjunction with the debit
col" means that you have both positive and negative (cr) in the same column?
If so, then the following will convert all the cr to negative and all the
ones without cr to positive.

Assume that the data 1062.22cr is in A2 for the fomula. Insert a helper
column to the right of the column for the formula. Note that the formula is
all one line although it breaks in this post.

=IF(IF(ISERROR(SEARCH("cr",A2,1)),0,SEARCH("cr",A2 ,1)-1)0,-VALUE(LEFT(A2,IF(ISERROR(SEARCH("cr",A2,1)),0,SEAR CH("cr",A2,1)-1))),VALUE(A2))

Now that you have the numbers as positive and negative, you can custom
format with the following custom format for the column.

0.00;0.00"cr"

The part before the semicolon is the positive formatting and the part after
the semicolon is the negative formatting.

The easy way to transfer the above formula to your worksheet is to go to any
blank worksheet and in cell A2 insert one of your numbers. Copy the above
formula and paste it into cell B2. Now on you main worksheet, insert a column
to the right of your column of data and simply select cell B2 on the blank
worksheet and copy it and paste it into the cell to the right of your data.
Excel will fix up the cell addressing for you.


--
Regards,

OssieMac


"JudithJubilee" wrote:

Hello Arceedee.

If your credits are in column A the:

=("-"&LEFT(A1,(FIND("c",A1)-1)))*1

Judith
--
Hope this helps


"Arceedee" wrote:

I'm importing a text file into Excel 2003 and by using the text to data
facility I'm almost able to create a 'financial' spreadsheet. The problem is
that there is a column that contains entries that are credits and appear like
1062.22cr
I want the figure to be a true number but cannot find a way to format the
column. I'm not bothered whether it appears as -1062.22 or 1062.22- or even
1062.22. I just need to have it have the ability to recognise the numbers and
to calculate. To have it recognised as a credit would be preferable so that
the column could be used in conjunction with the debit col to have a running
balance.
Thanks