formatting text/numbers

  • Thread starter Thread starter DAVID MCCLOSKEY
  • Start date Start date
D

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?
 
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.
 
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.
 
Back
Top