formatting text/numbers

[DAVID MCCLOSKEY]

I have entries in a column that are listed as a "credit" (ie. $1234CR). I
want to reformat these entries as -$1234 so that calculations can be done
correctly. Any ideas?

What about TRIM and multiply by -1?

 

[Don Guillett]

try
="-"&MID(D1,2,LEN(D1)-3)

 

[ryanb.]

if you want it as a number add this:

=VALUE("-"&MID(D2,2,LEN(D2)-3))

HTH

ryanb.

 

[Peo Sjoblom]

Or even

=---MID(D2,2,LEN(D2)-3)

 

[Don Guillett]

Don't think the value part is needed.

 

[Ture Magnusson]

David,

If the entries contain both positive and negatives, like this...

$1234CR
$1235
$1236CR
$1237CR
$1238
$1239

....you may try this non-formula approach. Follow carefully
step by step:

1. Enter -1 in an empty cell and copy that cell
2. Select the range with the list
3. Edit - Replace
Find what: $
Replace with: (leave it blank)
Click "Replace All"
Click "OK" and then "Close"
4. Edit - Goto, click "Special"
Select "Constants" and check the "Text" checkbox only
Click "OK"
5. Edit - Replace
Find what: CR
Replace with: (leave it blank)
Click "Replace All"
Click "OK" and then "Close"
6. Edit - Paste Special
Select "Values" and "Multiply"
Click "OK"
7. Select and clear the cell where you entered -1 in step 1.

 

[David]

Ture

Ingenious!

David

 

[Ture Magnusson]

David,

I'm happy that you liked it!

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

Ture

Ingenious!

David

 

[ryanb.]

What version of excel are you using? I am using XP and when I used your
formula it producesd a text result.
I had to either format text as number... or simply add the VALUE function...
or Peo's formula... just figured David needed a number to sum or add or
whatever.

ryanb.

 

[Don Guillett]

I am using xl2002 on xp home. Try my formula and =d2+3 in another cell.