minus sign

[Guest]

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.

 

[Don Guillett]

Sub moveminus()
For Each c In range("a2:a22")'Selection
c.Value = "-" & Left(c, Len(c) - 1)

Next
End Sub

 

[Bob Phillips]

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)

 

[Guest]

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