Remove Trailing Zero's

[BLKDAWG]

I have a bank file that I am trying to format in excel before loading as a .prn file into my financial software application for check recon. The dollar amount column must be imported in the financial database as a text field rather than a number field. The problem I'm having is that the file from the bank contains 3 decimal places(the last is a zero)rather than two. Once I format the file using VB macro in excel, I can't figure out how to remove the trailing zero's in the dollar amount column. I've tried changing the field to a number and removing the trailing zero, but when I try to change it back to a text field before exporting from excel, the numbers get messed up. RTrim only works for spaces. Any ideas?

 

[johnwm]

Try this:

format("123.450","#.00&quot Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[DarenNM]

JohnWM's solution will work well as long as it is always a zero, otherwise the number will be rounded up or down i.e.

Format("123.456","#.00")

would give 123.46

You could use

sStringVal = "123.450"
left$(sStringVal,len(sStringVal)-1)

You know how accountants like to be careful with numbers ;-)

Daren

 

[BLKDAWG]

Thanks for the help. I ended up using the left$ function and it worked great. In case anyone else needs the code, I had a column in excel that I needed to delete the trailing zero in the column for each row in the spreadsheet. Here's what I came up with. It may not be the best code, but it worked!

Set CurrentCell = ActiveSheet.Range("E1&quot
Do While Not IsEmpty(CurrentCell)
CurrentCell.Select
Length = Len(ActiveCell) - 1
ActiveCell = Left(ActiveCell, Length)
Set NextCell = CurrentCell.Offset(1, 0)
Set CurrentCell = NextCell
NextCell.Select
Loop