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

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

Well, in terms of time no real difference - half a second, but yours is still about 80% quicker, so if on a 6 figure data set, and a more complex formula, could add up to quite a lot I imagine. I'm definitely going to use that @ sign from now on!

Cheers
JB

 

[Fluff]

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

The @ sign is just a "place holder". The replace part of the formula replaces the @ with .address. Otherwise you'd write it like this

Evaluate("if(right(" & .Address & ",1)=""C"",left(" & .Address & ",len(" & .Address & ")-1)*-1,left(" & .Address & ",len(" & .Address & ")-1)*1)")

 

[bellman101]

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

ohhh, ok. Kind of a workaround to (at least physically) shorten a formula then. Again, genius! I do pride myself on my improv skills, but I've never picked up on that!

Thanks for the explanation, appreciated.

 

[Fluff]

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

Whilst I could take credit for it, I'd be lying
Rick Rothstein has kindly been helping me over the last month or so. So the credit goes to him

 

[Rick Rothstein]

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.

Odd... when I test it, the Text To Columns function has no problem ignoring the trailing space after the C and D are removed and successfully converting the remaining text to real numbers. I am using XL2010 on a Win8.1 system.

Whilst I could take credit for it, I'd be lying
Rick Rothstein has kindly been helping me over the last month or so. So the credit goes to him

I appreciate your acknowledging that.

ohhh, ok. Kind of a workaround to (at least physically) shorten a formula then. Again, genius! I do pride myself on my improv skills, but I've never picked up on that!

You are basically constructing an Excel formula for the Evaluate function to process. I find it easier to construct a formula if I don't have to break it up will a lot of concatenations (as Fluff showed you in Message #12 ) to get values stored in VBA variables into that formula. I usually use two stand in characters... an @ sign and a # sign. When the cell references are all in a single column so the reference might be...

"A2:A" & LastRow

throughout the formula, I use an @ sign to stand in for the complete address (the @ sign seems to suggest "address" to me) and when multiple columns are involved, I use a # sign for the last row (so I'll have something like A2:A# and B2:B# peppered throughout the formula) and then replace them afterwards.

 

[Fluff]

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

I appreciate your acknowledging that.

My pleasure. Without your "mentoring" I wouldn't know half as much as I doray:

 

[bellman101]

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

You are basically constructing an Excel formula for the Evaluate function to process. I find it easier to construct a formula if I don't have to break it up will a lot of concatenations (as Fluff showed you in Message #12 ) to get values stored in VBA variables into that formula. I usually use two stand in characters... an @ sign and a # sign. When the cell references are all in a single column so the reference might be...

"A2:A" & LastRow

throughout the formula, I use an @ sign to stand in for the complete address (the @ sign seems to suggest "address" to me) and when multiple columns are involved, I use a # sign for the last row (so I'll have something like A2:A# and B2:B# peppered throughout the formula) and then replace them afterwards.

Cracking set of tips you got there Rick!

 

[lmoseley7]

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

Thanks everyone for the discussion. The Find & Replace and Text to Columns trick is pretty slick.

Thanks again.

 

[Fluff]

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

Glad we could hep & thanks for the feedback