TXT FILE

[Guest]

Hel

I am downloading a text file with the extension (*.txt) into excel but the credit balances are followed by a CR. (eg 45,350.20CR). I need to be able to show them as a minus (eg -45,350.20 ). Does anyone know how to do this

Thank

Bluema

 

[Nikos Yannacopoulos]

Blueman,

Use an extra column to get the desired result with a formula like:
=VALUE(LEFT(A1,LEN(A1)-2))*-1
this will return what you want (assuming your first credit balance is in
A1 - or modify the cell reference accordingly), and copy down. The only
other thing to do is set the desired number format (the formula returns a
numeric value).

HTH,
Nikos

Help

I am downloading a text file with the extension (*.txt) into excel but the

credit balances are followed by a CR. (eg 45,350.20CR). I need to be able to
show them as a minus (eg -45,350.20 ). Does anyone know how to do this.

 

[Newbie]

I do exactly the samething but I make sure that the CR is imported into a
column of its own and then use the formula
Assuming the value is in A1 and the "CR" is in B1
IF(B1="CR",-A1,A1)

HTH

Help

I am downloading a text file with the extension (*.txt) into excel but the

credit balances are followed by a CR. (eg 45,350.20CR). I need to be able to
show them as a minus (eg -45,350.20 ). Does anyone know how to do this.

 

[Guest]

Thanks Niko

Only problem is that there are debit ballances in same column that are OK by using this formula the are converted to minus too

Blueman

 

[Guest]

Thanks Newbie that works

blueman

 

[Nikos Yannacopoulos]

OK then, use this formula instead:
=VALUE(LEFT(A1,LEN(A1)-2))*IF(RIGHT(A1,2)="CR",-1,1)

HTH,
Nikos

Thanks Nikos

Only problem is that there are debit ballances in same column that are OK

by using this formula the are converted to minus too.