TXT FILE

B

BLUEMAN

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
 
N

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

BLUEMAN said:
Help

I am downloading a text file with the extension (*.txt) into excel but the
Click to expand...
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.
 
N

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
BLUEMAN said:
Help

I am downloading a text file with the extension (*.txt) into excel but the
Click to expand...
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.
 
B

blueman

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
 
N

Nikos Yannacopoulos

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

HTH,
Nikos


blueman said:
Thanks Nikos

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