Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
hi
while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
Try this.
Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this. Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ///Make sure Trailing minus for negative numbers is checked /// hi Biff there is no option in the advanced text import settings for trailing minus.! the options available are "Decimal seperator" & "Thousand seperator" i'm using ms office 2000 premium.! -via135 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
Try this macro from Dana DeLouis.
Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub Select the range of cells in question and run the macro. Post back if you need help on how to run a macro. -- Biff Microsoft Excel MVP "via135" wrote in message ... On Nov 24, 11:24 pm, "T. Valko" wrote: Try this. Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ///Make sure Trailing minus for negative numbers is checked /// hi Biff there is no option in the advanced text import settings for trailing minus.! the options available are "Decimal seperator" & "Thousand seperator" i'm using ms office 2000 premium.! -via135 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
Hi,
I use 2000 premium also. Try it this way. DataText to Columns Check Delimited Click Next Check Other and type in - Click Finish You can also do this with EditReplace Find what: - Replace with: Leave this blank Click Replace all. HTH Martin "via135" wrote in message ... On Nov 24, 11:24 pm, "T. Valko" wrote: Try this. Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ///Make sure Trailing minus for negative numbers is checked /// hi Biff there is no option in the advanced text import settings for trailing minus.! the options available are "Decimal seperator" & "Thousand seperator" i'm using ms office 2000 premium.! -via135 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
That removes the trailing minus changing the sign of the original value.
It's my understanding that they still want to retain the values as negatives. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi, I use 2000 premium also. Try it this way. DataText to Columns Check Delimited Click Next Check Other and type in - Click Finish You can also do this with EditReplace Find what: - Replace with: Leave this blank Click Replace all. HTH Martin "via135" wrote in message ... On Nov 24, 11:24 pm, "T. Valko" wrote: Try this. Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ///Make sure Trailing minus for negative numbers is checked /// hi Biff there is no option in the advanced text import settings for trailing minus.! the options available are "Decimal seperator" & "Thousand seperator" i'm using ms office 2000 premium.! -via135 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
Uhuh, missed that bit!!
"T. Valko" wrote in message ... That removes the trailing minus changing the sign of the original value. It's my understanding that they still want to retain the values as negatives. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi, I use 2000 premium also. Try it this way. DataText to Columns Check Delimited Click Next Check Other and type in - Click Finish You can also do this with EditReplace Find what: - Replace with: Leave this blank Click Replace all. HTH Martin "via135" wrote in message ... On Nov 24, 11:24 pm, "T. Valko" wrote: Try this. Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ///Make sure Trailing minus for negative numbers is checked /// hi Biff there is no option in the advanced text import settings for trailing minus.! the options available are "Decimal seperator" & "Thousand seperator" i'm using ms office 2000 premium.! -via135 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
On Nov 25, 2:29 am, "T. Valko" wrote:
Try this macro from Dana DeLouis. Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub Select the range of cells in question and run the macro. Post back if you need help on how to run a macro. -- Biff Microsoft Excel MVP "via135" wrote in message ... On Nov 24, 11:24 pm, "T. Valko" wrote: Try this. Select the range of cells in question Goto the menu DataText to Columns Click Next twice Click Advanced Make sure Trailing minus for negative numbers is checked OK Finish -- Biff Microsoft Excel MVP "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ///Make sure Trailing minus for negative numbers is checked /// hi Biff there is no option in the advanced text import settings for trailing minus.! the options available are "Decimal seperator" & "Thousand seperator" i'm using ms office 2000 premium.! -via135- Hide quoted text - - Show quoted text - hi Biff yes it is..thks..works like a charm! -via135 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
If there is a mix of postive and negative values
this may be helpful for a one-time-fix: 1) Set the format to an appropriate number format. 2) Put a 1 in an empty cell and copy that cell 3) Select the range to be impacted 4) <edit<paste special....Check: Multiply....Click [OK] Now, all of the postive values have become numbers. The negative values are still text 5) Put a -1 in a cell and copy the cell Note: you'll be in Copy Mode for the next few steps 6) Select the range to be impacted 7) Press the [F5] key...Click: Special Check: Formulas Check: Text.....UNcheck the other options Click [OK] Now, only the negative values are selected 8) <edit<replace Find What: -........that's a minus sign Replace with: (leave this blank) Click [Replace All] Press [ESC] twice....to clear the result message and the edit/replace window. NOte: you are STILL in Copy Mode AND the text values are STILL selected 9) <edit<paste special....Check: Multiply....Click [OK] Now, all of the negative values have become negative numbers. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
"via135" wrote in message
... On Nov 25, 2:29 am, "T. Valko" wrote: Try this macro from Dana DeLouis. Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub Select the range of cells in question and run the macro. Post back if you need help on how to run a macro. hi Biff yes it is..thks..works like a charm! -via135 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trailing minus
On Nov 25, 7:15 pm, "Ron Coderre"
wrote: If there is a mix of postive and negative values this may be helpful for a one-time-fix: 1) Set the format to an appropriate number format. 2) Put a 1 in an empty cell and copy that cell 3) Select the range to be impacted 4) <edit<paste special....Check: Multiply....Click [OK] Now, all of the postive values have become numbers. The negative values are still text 5) Put a -1 in a cell and copy the cell Note: you'll be in Copy Mode for the next few steps 6) Select the range to be impacted 7) Press the [F5] key...Click: Special Check: Formulas Check: Text.....UNcheck the other options Click [OK] Now, only the negative values are selected 8) <edit<replace Find What: -........that's a minus sign Replace with: (leave this blank) Click [Replace All] Press [ESC] twice....to clear the result message and the edit/replace window. NOte: you are STILL in Copy Mode AND the text values are STILL selected 9) <edit<paste special....Check: Multiply....Click [OK] Now, all of the negative values have become negative numbers. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "via135" wrote in message ... hi while importing a txt file into xl there is one column which is in txt format having figures with trailing minus (ex: 100-, 150-,100000-). how can i change them into real numbers for arithmetical operations??? added to the head ache is that the column is aligned horizontally centred!!! any hlp pl?! -via135- Hide quoted text - - Show quoted text - ###7) Press the [F5] key...Click: Special Check: Formulas Check: Text.....UNcheck the other options Click [OK] Now, only the negative values are selected### sorry ron..! i'm getting error "no cells were found" -via135 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) | |||
Trailing Graphs | Excel Discussion (Misc queries) | |||
.xls trailing on end of name for workbook | Excel Discussion (Misc queries) | |||
Trailing blanks | Excel Discussion (Misc queries) | |||
Importing values w/trailing minus signs | Excel Discussion (Misc queries) |