Assume Decimals

Mar 5th, 2002, 10:49 AM

I am having a problem writing a piece of code in excel to move a dollar figure ie. 458.23 to 45823. The bank wants to assume the decimal then format it. So it would look something like this 000000045823. It has to be in VB because I import all the info from a text file format it and convert it back to a txt file for the bank.

Steve Hartman · Mar 5th, 2002, 10:53 AM

Why not just multiply by 100?

Mar 5th, 2002, 10:58 AM

I need a piece of code. I am using numberformat in VB to assume the "0" in front of the amount. I need something to either move the number to the left 2 spaces. Or a formula to use inside the cell to multiply by 100

Steve Hartman · Mar 5th, 2002, 11:17 AM

How is the text file you import laid out? One column or multi column? If Multi column, how is it delimited? You should be able to do the multiplcation when you import the file.

Mar 5th, 2002, 11:22 AM

The file is delimited with comas. Do you know code that would multiply when I import it?

Mar 5th, 2002, 11:27 AM

I have time to explain a little now. I have a checking statement that gets exported from a UNIX box. It exports account number, check number, amount, and date. They all have to have leading "0" which I did with formatnumber. I have everything working except the amount field when I export. When I format the amount I lose the decimal. So if the txt file from the UNIX system says 452.23 I import it, format it using formatnumber("000000000000"). I get 000000000452. I am missing something simple.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 10:49 AM	#1
Guest Posts: n/a	I am having a problem writing a piece of code in excel to move a dollar figure ie. 458.23 to 45823. The bank wants to assume the decimal then format it. So it would look something like this 000000045823. It has to be in VB because I import all the info from a text file format it and convert it back to a txt file for the bank.

Mar 5th, 2002, 10:53 AM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Why not just multiply by 100?

Mar 5th, 2002, 10:58 AM	#3
Guest Posts: n/a	I need a piece of code. I am using numberformat in VB to assume the "0" in front of the amount. I need something to either move the number to the left 2 spaces. Or a formula to use inside the cell to multiply by 100

Mar 5th, 2002, 11:17 AM	#4
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	How is the text file you import laid out? One column or multi column? If Multi column, how is it delimited? You should be able to do the multiplcation when you import the file.

Mar 5th, 2002, 11:22 AM	#5
Guest Posts: n/a	The file is delimited with comas. Do you know code that would multiply when I import it?

Mar 5th, 2002, 11:27 AM	#6
Guest Posts: n/a	I have time to explain a little now. I have a checking statement that gets exported from a UNIX box. It exports account number, check number, amount, and date. They all have to have leading "0" which I did with formatnumber. I have everything working except the amount field when I export. When I format the amount I lose the decimal. So if the txt file from the UNIX system says 452.23 I import it, format it using formatnumber("000000000000"). I get 000000000452. I am missing something simple.