Excel Number String - VBA formating

[jkrenkel]

I am creating a text file using excel spreadsheet. I have a handle on the actually formatting of the text file, the problem I have is that the way the numbers need to be converted are difficult. For example, the number format for 1000.00000 would be 0100000000 and for 978.7562 would be 009787562000000 and needs to be padded out 14spaces with 0 filler if it goes out that far. I know I can probably bump the decimal places out to acheive that requirement, but here is where i am having issues.

First i need to know how to pad the beginning of the number with the 0's. I am not sure of that. And secondly, how do i remove the decimal mark, but continue with the number string.

basically i am using a cell reference and then a format refernece in my VBA.... but i cannot get it working right.

If I use format "000000000000" I get this result 00000000000431 if i use the "#########" format i get this result 431 and and the excel number is 430.80939948 and it should be 0043080939948

Ideas on the VBA code to make this happen. Or if i can parse out the decimal in excel and add the padded 0 at the beginning?

 

[pgc01]

Hi jkrenkel

Example for A1:

range("A1")= right(string(14,"0")&replace(range("A1"),".",""),14)

Remark:

The cell MUST be formatted as text.

If it's not, either format it as text before

 Range("A1").NumberFormat = "@"

or force excel to take the value as string:

Range("A1") = "'" & Right(String(14, "0") & Replace(Range("A1"), ".", ""), 14)

 

[jkrenkel]

That worked for getting the string to read the 14 digits. however i still have two things going on.

430.809399477807000

I still get the decimal and it is not padded to the 10,000 digit. so this should read

00430809399.......

Any ideas.

Thanks for the reply..

 

[pgc01]

Hi

I was just padding to the 14 digits. If I understand well now, you want the integer part to be always 5 digits:

Try:

MsgBox Replace(Format(Range("A1"), "00000.000000000"), ".", "")

 

[jkrenkel]

Ok I messed up a little, the code works fine sort of. I used the wrong call so i was formatting the wrong thing. So i fixed it and it replaced the decimal but the number changed

so 430.809399 was converted to the text string of 30809399477807 look like the 4 is missing.

Also, that works perfectly to remove the decimal. But it what i am trying to get it to do is to look like this

430.809399 would be 00430809399
1000.000 would be 0100000000
89.809399 would be 00089890399

i hope the picture is becoming a bit more clear

 

[Scott Huish]

None of those results you wanted are 14 characters long, the 1st and 3rd are 11, and the second is 10.

 

[jkrenkel]

Ok imagine that they were 14. and the rest of the digits would be 0 if they had to be.

 

[Scott Huish]

How did pgc01's code not work then.

For 430.809399 it gave 00430809399000
For 1000 it gave 01000000000000
For 89.809399 it gave 00089809399000