I wish I had a macro for this?

[WLHagen]

Hello friends!

I'm using Excel 2007 and work at a financial institution preparing financial reports. My core accounting system shows negative numbers using the "<" and ">". For example, negative 20.00 is shown as "<20.00>" without the quotes, that is. When I open the file, I can chop off an eliminate the right symbol, but not the left symbol - especially due to the fact that I have columns of numbers of varying lengths - some of which are positive numbers, and some of which are negative numbers.

I would like to create a macro that would perform the following actions on the selected cell (that is, I have already selected the cell I want to change):

Press F2 (to enter cell editing mode).
Press Home key (to bring cursor to the beginning of the cell contents)
Press Delete key (to delete the left wedge)
Insert the minus ("-") key
Press Enter.

I tried to record the macro, but it replaced the cell contents with what was in the cell that I used to record the macro.

I'm sure this can be done, but I'm stumped.

 

[Jonmo1]

Try using Find/Replace from the Edit Menu...

Find What: <
Replace With: -
Replace All

 

[Greg Truby]

Normally I would ask you what code you've written thus far and make you sweat it out. For some odd reason I'm doing you a disservice and just spoonfeeding you the answer. My apology. Please study the following:

Sub GTLTtoNegatives()
    Dim celItem     As Excel.Range, _
        strFormula  As String
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    For Each celItem In Selection.Cells
    
        Let strFormula = celItem.Formula
        
        If Left(strFormula, 1) = "<" Then
            Let strFormula = "-" & Mid(strFormula, 2, Len(strFormula) - 2)
            Let celItem.Formula = strFormula
        End If
    
    Next celItem
    
End Sub

 

[AD_Taylor]

You could probably also do this via a formula:

= "-" & LEFT( RIGHT( A1, LEN(A1) - 1), LEN(A1) - 2)

 

[Greg Truby]

Try using Find/Replace from the Edit Menu...

Find What: <
Replace With: -
Replace All

Well, sure if you want to do it efficiently...

In code that'd look about like so...

Sub GTLTtoNegativesFasterAndSmarter()
 
    If TypeName(Selection) <> "Range" Then Exit Sub
 
    With Selection
 
        .Replace What:=">", _
                 Replacement:="", _
                 LookAt:=xlPart
        .Replace What:="<", _
                 Replacement:="-", _
                 LookAt:=xlPart
 
    End With
End Sub

 

[Jonmo1]

Well, sure if you want to do it efficiently...

Who wants to be efficient?

 

[Greg Truby]

= "-" & LEFT( RIGHT( A1, LEN(A1) - 1), LEN(A1) - 2)

Here the OP is wanting to permanently modify imported values. And while, yes, one could certainly construct a formula to do this, it would actually be more laborious to do so and then come back and copy the formulas' outputs and over-paste them as values and then delete the interim column. Also - your formula needs to use some type of conditional logic (an IF() function) to test for the presence of the "<" or ">" sign before doing its thing. :wink:

 

[WLHagen]

Greg - thank you SOOOO much. I apologize that I didn't post my code here, but the gist of it is in my OP. Also, apologize for the delay - sometimes, my boss is more insistent that I get the work DONE than on "improving the process to make it easier next time"!

ANYway - I like the "Faster and Smarter" macro, as it works regardless of whether I import the number with the rightmost wedge or not. When I used your first code, it deleted the rightmost character from the cells, even if that rightmost character was not the rightwedge, and was part of my actual cell data! And us CPAs don't like it when things don't balance "to the penny"!

To make a long story even longer - thank you again for coming to my rescue. You have saved me and my cohorts lots and lots of time!