Converting Numbers with C or D Suffix to Positive or Negative Amounts

[lmoseley7]

One of our accounting systems will export amounts with a suffix of " D" for debit balances (positive for non-accountants) and a " C" for Credit balances (negative). Obviously I can find and replace the " D" for my positive amounts, but I need to convert the ones with " C" to negatives. I can write a formula to do the conversion for me, but I hate having to use helper columns unless totally necessary and I'm hoping for a solution that I can roll out to users that aren't very Excel savvy. I was hoping there would be a creative number format that would convert suffixes to a positive or negative. I could write a macro to handle it or even a UDF potentially, but that would require me to process the data in another column and either reference that column or replace the existing contents.

Does anyone have any suggestions as to how I could accomplish a conversion within the same cells without the use of any helper columns?

Some examples:

31,012,685.81 C
309,949.33 D
2,965.41 C

 

[bellman101]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

Hi,

I don't think you can get away with that without VBA, but you wouldn't need an extra column.

There's probably a far more clever way of doing this in 1 without looping, but this should certainly get the job done.

Sub ConvertBalances()


    For Each ForCell In Range("A1:A1000").Cells
    MsgBox ForCell.Value
        If Right(ForCell.Value, 1) = "D" Then
            ForCell.Value = -(Left(ForCell.Value, Len(ForCell.Value) - 1) * 1)
        Else
            ForCell.Value = (Left(ForCell.Value, Len(ForCell.Value) - 1) * 1)
        End If
    Next


End Sub

Adjust A1:A1000 accordingly. Preferably, Change it to 'Range("A1").resize(NumberOfRows).Cells', or something similarly dynamic, if you know how to get row count into a variable.

Hope that gets you started.

Cheers
JB

 

[Fluff]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

Alternatively, a non looping code

Sub RemoveSuffix()
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(right(@,1)=""C"",left(@,len(@)-1)*-1,left(@,len(@)-1)*1)", "@", .Address))
    End With
End Sub

This assumes the values are in A2 downwards

 

[bellman101]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

Alternatively, a non looping code

See, knew somebody would come up with something!

Thanks for that @Fluff - learned something new today!

In terms of system resources, do you know if that performs noticeably better than a loop? or does it actually still just perform a loop in the background?

 

[Rick Rothstein]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

You can do this easily enough without code as well. First, select the cells (or full column if you wish) and open the Replace dialog box, then replace D with nothing and replace C with - (a minus sign), then close the Replace dialog box and then, with the cells still selected, open the Text To Columns, select Delimited and make sure the Comma checkbox is not checked, then click the Finish button.

 

[bellman101]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

You can do this easily enough without code as well. First, select the cells (or full column if you wish) and open the Replace dialog box, then replace D with nothing and replace C with - (a minus sign), then close the Replace dialog box and then, with the cells still selected, open the Text To Columns, select Delimited and make sure the Comma checkbox is not checked, then click the Finish button.

Hi Rick,

How does that switch the minus to the start to leave a negative numeric value?

 

[Fluff]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

See, knew somebody would come up with something!

Thanks for that @Fluff - learned something new today!

In terms of system resources, do you know if that performs noticeably better than a loop? or does it actually still just perform a loop in the background?

I've no idea of the internal workings of Evaluate. (ie if it uses an internal loop or not), so cannot answer that
But having just done a test on 20,000 rows, my code took 0.08 seconds, whilst yours took 0.6 seconds. so realistically no difference

 

[Scott T]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

Hi Rick,

How does that switch the minus to the start to leave a negative numeric value?

Even though the minus sign is at the end, text to columns still recognizes that it is a negative number and moves the sign to the front.

 

[Rick Rothstein]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

How does that switch the minus to the start to leave a negative numeric value?

Some places around the world use a trailing minus sign for negative numbers instead of a leading minus sign, to the Text To Columns dialog respects that notation... I guess if assumes if your text numbers have a trailing minus sign, then you must be using that notation and it automatically figures that the text is supposed to be a negative number... Excel, without using Cell Formatting, defaults the minus sign to the front for negative values and so that is how it displays the negative value it got from the text string in the cell.

 

[bellman101]

Re: Need Help Converting Numbers with C or D Suffix to Positive or Negative Amounts

Ah ok, when I tried it, it didn't work, but I think that's maybe due to the space before C/D?

Actually just tested that quickly. So if you replace " D" instead of "D", and same for C, it does work.

It's strange though that while this feature recognises that, excel in general does not. Just tried =A2*1 and it just gives a #VALUE error.