how to convert 123- to -1.23 with a macro

[cheri gemini]

I copy data from our main frame and the number results for negative numbers
show the "-" sign behind the number. Excel considers this a text field. I
tried copying a macro but the answer is always -1.23 even if I start with
456-. I want to convert the number when my cursor is on the field and I
select a unique ctrl F function.

Any suggestions?

 

[L. Howard Kittle]

Hi Cheri,

Try this, which I just saw a couple days ago.

Select the 456- (or a column of them)

Data > Text to columns > Finish.

Be sure "Trailing minus for negative numbers" is checked. Find it in the
Advanced window after clicking Next>.

HTH
Regards,
Howard

 

[Gord Dibben]

Sub Negsignleft()
Dim Cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each Cell In rng
If IsNumeric(Cell.Value) Then
Cell.Value = CDbl(Cell.Value) * 1
End If
Next Cell
End Sub

You can do this without a macro if you want.

Data>Text to Columns>Next>Next>Advanced.....make sure "Trailing minus for
negative numbers" is checkmarked.


Gord Dibben MS Excel MVP

 

[L. Howard Kittle]

Whoops, you said you wanted a macro. Try this, from the macro recorder and
assigned a keyboard shortcut of ctrl+m.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 11/9/2009 by L. Howard Kittle
'
' Keyboard Shortcut: Ctrl+m
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

HTH
Regards,
Howard

 

[Rick Rothstein]

If you leave the On Error Resume Next active, you can simplify your code
somewhat...

Sub MakeNormal()
Dim Cell As Range
On Error Resume Next
For Each Cell In Selection.SpecialCells(xlCellTypeConstants)
Cell.Value = CDbl(Cell.Value)
Next
End Sub

 

[Gord Dibben]

Thanks Rick

 

[OssieMac]

Hello Cheri,

Ctrl/f opens the Find and Replace dialog box and Ctrl/Shift/f opens the
Format cells dialog box. Does not seem appropriate to use these short cuts.

How about using Double Click the cell. I was uncertain if you also wanted to
divide the number by 100 to finish up with a decimal number and have
therefore provide 2 options of code. The first one simply converts the
number, the second converts and divides by 100.

To install the code:
Right click the tab sheet name
Select View code
Copy the code (between the asterisk lines) into the VBA editor
Close the editor (Cross with red background top right)

'****************************************
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

If Right(Target, 1) = "-" Then
Target = "-" & Left(Target, Len(Target) - 1)
End If

Cancel = True
End Sub
'*****************************************

Now when you double click a cell if it has a trailing negative sign it will
be converted to the leading negative sign.

Following code is similar. Use in lieu of above if you want the result
divided by 100.

'****************************************
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

If Right(Target, 1) = "-" Then
Target = ("-" & Left(Target, Len(Target) - 1)) / 100
End If

Cancel = True
End Sub
'*****************************************