Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
Have you tried (Ctrl+ H) Find and replace with blank..
If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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" wrote: Have you tried (Ctrl+ H) Find and replace with blank.. If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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 --------------- Jacob Skaria "Meeru" wrote: 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" wrote: Have you tried (Ctrl+ H) Find and replace with blank.. If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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" wrote: 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 --------------- Jacob Skaria "Meeru" wrote: 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" wrote: Have you tried (Ctrl+ H) Find and replace with blank.. If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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 --------------- Jacob Skaria "Meeru" wrote: 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" wrote: 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 --------------- Jacob Skaria "Meeru" wrote: 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" wrote: Have you tried (Ctrl+ H) Find and replace with blank.. If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
Use Data/ Text to Columns.
-- David Biddulph "Meeru" wrote in message ... 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" wrote: Have you tried (Ctrl+ H) Find and replace with blank.. If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
Your Simple Answer solved my problem which took ages for me to fix.
Thanks a ton. "Jacob Skaria" wrote: Have you tried (Ctrl+ H) Find and replace with blank.. If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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 DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OKFinish To remove it completely (assuming it's the only dash in the cell): Select the cell(s) in question Goto EditReplace Find what: - Replace with: nothing, leave this blank Replace All -- Biff Microsoft Excel MVP "Meeru" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
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 "T. Valko" wrote: 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 DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OKFinish To remove it completely (assuming it's the only dash in the cell): Select the cell(s) in question Goto EditReplace Find what: - Replace with: nothing, leave this blank Replace All -- Biff Microsoft Excel MVP "Meeru" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a - sign after the number
I see you have a solution.
-- Biff Microsoft Excel MVP "Meeru" wrote in message ... 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 "T. Valko" wrote: 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 DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OKFinish To remove it completely (assuming it's the only dash in the cell): Select the cell(s) in question Goto EditReplace Find what: - Replace with: nothing, leave this blank Replace All -- Biff Microsoft Excel MVP "Meeru" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value Axis - How to remove percent sign without changing values? | Charts and Charting in Excel | |||
remove convert/extract the number from'12345.56; ie remove ' sign | Excel Worksheet Functions | |||
how do I remove the % sign in cells without using the % icon | Excel Discussion (Misc queries) | |||
I want to add a plus sign behind a number and keep the leading zer | Excel Worksheet Functions | |||
Reversing the sign of a number | Excel Discussion (Misc queries) |