Moving a symbol within a cell

[Al]

On imported data I often have the minus sign for a number
appear on the right side of the numbers instead of the
left side, I need to move it to the left so the cell data
is a number and not text. Also not all numbers in the
sheet are negative numbers.

Is there a way to move this symbol within a pertinent cell
without affecting the rest of the data and not doing it
manually cell by cell.

 

[AlfD]

Hi!

Have a look at

http://www.asap-utilities.com/

which has an excellent freely-downloadable Add-In.
One of the utilities in it does what you are looking for.

Alternatively, put =--("-"&LEFT(A1,LEN(A1)-1)) in a cell to change A
from, say, 1234- (text) to -1234 (number).

Al

 

[Freemini]

You could use a healper column with the following formula in say D5 wit
your original number in C5:

=IF(RIGHT(C5)="-",VALUE(LEFT(C5,FIND("-",C5)-1))*-1,C5)

Just copy this down the column.

hth

Mik

 

[Ragdyer]

One way is to add a "helper" column that contains a formula that will revise
the data.
You could then remove the formula, leaving the corrected data behind.

With imported data in column A, try this in an adjoining column:

=IF(ISERROR(A1*1),-LEFT(A1,LEN(A1)-1),A1)

And copy down as needed.

If column A is very long, after entering the formula in B1, you could
*double* click on the "fill handle", to *automatically* copy the formula in
B1, down column B, as far as there is data in column A.

While the column is *still* selected, right click in the selection, and
choose "Copy".
Right click again, and choose "PasteSpecial", and click on "Values", then
<OK>.

You now have a column of the revised numbers only, where you could now
delete the original if you wish.

 

[JE McGimpsey]

One more option:

http://www.mcgimpsey.com/excel/postfixnegatives.html

 

[Al]

Thank you, this works very well.