Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
Hi! I've got a large workbook containing many numbers that should be
negative. For some reason, the - is showing up at the end of the number. I believe the document was converted to Excel. Anyway, I want to quickly move the - from the end to the beginning of the number, then format the cell as number. Any ideas how to do that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
On Tue, 1 Apr 2008 06:35:01 -0700, Giggly4g
wrote: Hi! I've got a large workbook containing many numbers that should be negative. For some reason, the - is showing up at the end of the number. I believe the document was converted to Excel. Anyway, I want to quickly move the - from the end to the beginning of the number, then format the cell as number. Any ideas how to do that? Select the cells, one column at a time. Then Select Data/Text to columns (or on the Data tab of 2007) At step 3 of the Wizard, select Advanced and ensure that "Trailing minus for negative numbers" is Set. Finish. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
Data/ Text to columns/ ... should do the trick. One stage of the wizard has
an Advanced menu, including the option for trailing minus (which may be set by default). -- David Biddulph "Giggly4g" wrote in message ... Hi! I've got a large workbook containing many numbers that should be negative. For some reason, the - is showing up at the end of the number. I believe the document was converted to Excel. Anyway, I want to quickly move the - from the end to the beginning of the number, then format the cell as number. Any ideas how to do that? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
Use this ....
Select the range first .. Sub reverse() For Each r In Selection With r .Value = -(.Value) End With Selection.Value = -r Next r End Sub On Apr 1, 7:31*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Data/ Text to columns/ ... should do the trick. *One stage of the wizard has an Advanced menu, including the option for trailing minus (which may be set by default). -- David Biddulph "Giggly4g" wrote in message ... Hi! I've got a large workbook containing many numbers that should be negative. For some reason, the - is showing up at the end of the number. I believe the document was converted to Excel. Anyway, I want to quickly move the - from the end to the beginning of the number, then format the cell as number. Any ideas how to do that?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
Excel doesn't like .Value = -(.Value)
I tried using (.Value)- since the - is at the end. Didn't work. I'm getting a syntax error message. I'd try using text to columns but there are over 800 columns to do. "muddan madhu" wrote: Use this .... Select the range first .. Sub reverse() For Each r In Selection With r .Value = -(.Value) End With Selection.Value = -r Next r End Sub On Apr 1, 7:31 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Data/ Text to columns/ ... should do the trick. One stage of the wizard has an Advanced menu, including the option for trailing minus (which may be set by default). -- David Biddulph "Giggly4g" wrote in message ... Hi! I've got a large workbook containing many numbers that should be negative. For some reason, the - is showing up at the end of the number. I believe the document was converted to Excel. Anyway, I want to quickly move the - from the end to the beginning of the number, then format the cell as number. Any ideas how to do that?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
On Tue, 1 Apr 2008 09:21:04 -0700, Giggly4g
wrote: I'd try using text to columns but there are over 800 columns to do. Try this sub, then: ========================= Option Explicit Sub TrailingNeg() Dim c As Range For Each c In Cells.SpecialCells(xlCellTypeConstants) With c If IsNumeric(.Value) And _ Right(.Value, 1) = "-" Then .Value = Val(.Value) * -1 End If End With Next c End Sub =========================== --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to move - from end of number to beginning
try this
.value = .value It's working ... On Apr 1, 9:21*pm, Giggly4g wrote: Excel doesn't like .Value = -(.Value) I tried using (.Value)- since the - is at the end. Didn't work. I'm getting a syntax error message. I'd try using text to columns but there are over 800 columns to do. "muddan madhu" wrote: Use this .... Select the range first .. Sub reverse() For Each r In Selection With r .Value = -(.Value) End With Selection.Value = -r Next r End Sub On Apr 1, 7:31 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Data/ Text to columns/ ... should do the trick. *One stage of the wizard has an Advanced menu, including the option for trailing minus (which may be set by default). -- David Biddulph "Giggly4g" wrote in message ... Hi! I've got a large workbook containing many numbers that should be negative. For some reason, the - is showing up at the end of the number. I believe the document was converted to Excel. Anyway, I want to quickly move the - from the end to the beginning of the number, then format the cell as number. Any ideas how to do that?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I keep the 0 at the beginning of a number, ex. 090? | New Users to Excel | |||
shortcut key to move to beginning of new row | Excel Worksheet Functions | |||
need enter key to move to the next column beginning | Excel Worksheet Functions | |||
Zero at the beginning of a number | Excel Discussion (Misc queries) | |||
Shortcut key to move to beginning of a worksheet | Excel Discussion (Misc queries) |