How to remove a - sign after the number

[Meeru]

The number extraced from our financial software shows a minus sign after the
number. for e.g
123,768-
i cannot do any calculation on this numbers as it does not recognise it as a
number, any qick way to delete the - (minus) sign after the number.
thanks in advance
meeru

 

[Jacob Skaria]

Have you tried (Ctrl+ H) Find and replace with blank..

If this post helps click Yes

 

[T. Valko]

Do you want to remove it or relocate it to the front of the number making it
a negative number?

If you want to relocate it...

Select the cell(s) in question
Goto Data>Text to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK>Finish

To remove it completely (assuming it's the only dash in the cell):

Select the cell(s) in question
Goto Edit>Replace
Find what: -
Replace with: nothing, leave this blank
Replace All

 

[Meeru]

thanks Valko, i want the first option remove and replace, but i can do for
this for selecting only one column at a time. Since i have many numbers in 12
columns ( Jan to Dec ), how can i select the 12 column and do it at one go as
you suggested in option 1.
thanks
meeru

 

[Meeru]

Thanks Jacob, i have tried that, but it removes the - but i would like to
have it placed at the beginning of the number.

thanks
meeru

 

[Jacob Skaria]

Hi Meeru

Try the below macro to convert multiple columns..If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Select the columns to be converted and run macro from Tools|Macro|Run
<selected macro()>


Sub Macro()
For Each cell In Selection
If Right(cell.Text, 1) = "-" Then
cell.Value = -Left(cell.Text, Len(cell.Text) - 1)
End If
Next
End Sub

If this post helps click Yes

 

[Meeru]

Hi Jacob,

Many thanks it worked so well. May i also ask one thing additional, can it
format the numbers also to #,### format.
thanks in advance.
meeran

 

[Jacob Skaria]

Sure Meeru; see below..

Sub Macro()
For Each cell In Selection
If Right(cell.Text, 1) = "-" Then
cell.Value = -Left(cell.Text, Len(cell.Text) - 1)
End If
Next
Selection.Numberformat = "#,###"
End Sub

If this post helps click Yes

 

[T. Valko]

I see you have a solution.

 

[Vk]

Your Simple Answer solved my problem which took ages for me to fix.
Thanks a ton.