Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
minus sign
when importing a file from bus obs i get a column of data where the minus
sign for negative numbers only comes after the last character,( 435-) can anyone supply me with a function to move the minus sign to the beginning (-435). Thanks once again everyone. -- delmac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
minus sign
Sub moveminus()
For Each c In range("a2:a22")'Selection c.Value = "-" & Left(c, Len(c) - 1) Next End Sub -- Don Guillett SalesAid Software "delmac" wrote in message ... when importing a file from bus obs i get a column of data where the minus sign for negative numbers only comes after the last character,( 435-) can anyone supply me with a function to move the minus sign to the beginning (-435). Thanks once again everyone. -- delmac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
minus sign
Function FormatCells()
Dim cell As Range For Each cell In Selection If Right(cell.Value, 1) = "-" Then cell.Value = Val("-" & Left(cell.Value, Len(cell.Value) - 1)) End If Next cell End Function -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "delmac" wrote in message ... when importing a file from bus obs i get a column of data where the minus sign for negative numbers only comes after the last character,( 435-) can anyone supply me with a function to move the minus sign to the beginning (-435). Thanks once again everyone. -- delmac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
minus sign
What kind of file is the one you are importing?
I did a few quick tests with XL2003 and a text file. When you open the file, XL automatically initiates the Text Import wizard. In step 3, select the column that can contain trailing minus signs, click the Advanced button and ensure 'Trailing minus for negative numbers' is checked. If you want to do the same programmatically, record a macro while opening the file through the UI. A sample result would be (watch out for line wraps): Workbooks.OpenText Filename:= _ "C:\Temp\Book3.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _ TrailingMinusNumbers:=True -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "delmac" wrote: when importing a file from bus obs i get a column of data where the minus sign for negative numbers only comes after the last character,( 435-) can anyone supply me with a function to move the minus sign to the beginning (-435). Thanks once again everyone. -- delmac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minus Sign | Excel Discussion (Misc queries) | |||
I do not want the minus sign | Excel Worksheet Functions | |||
minus sign | Excel Worksheet Functions | |||
plus and minus sign in cells | Excel Worksheet Functions | |||
I want to display the minus sign | New Users to Excel |