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?

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

"cheri gemini" <(E-Mail Removed)> wrote in message
news:4FA18955-6C12-41CB-99DC-(E-Mail Removed)...
>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?

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

On Mon, 9 Nov 2009 15:00:10 -0800, cheri gemini
<(E-Mail Removed)> wrote:

>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?

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

"cheri gemini" <(E-Mail Removed)> wrote in message
news:4FA18955-6C12-41CB-99DC-(E-Mail Removed)...
>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?

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> 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
>
> On Mon, 9 Nov 2009 15:00:10 -0800, cheri gemini
> <(E-Mail Removed)> wrote:
>
>>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?
>

Thanks Rick

On Mon, 9 Nov 2009 19:22:12 -0500, "Rick Rothstein"
<(E-Mail Removed)> wrote:

>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

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
'*****************************************


--
Regards,

OssieMac