VBA function to remove decimal AND have a fixed length

[john69]

Hello all, I am a practicing VBA'er but this one is really tricky for me.

I have a set of weights that may range from .01 lb all the way to 1.5 lbs lbs all with different decimal points..

Example:

Before__________After
0.1845 becomes 001845
1.054 becomes 010540
1.16 becomes 011600
1.116 becomes 011160
0.278 becomes 002780
0.91 becomes 009100

What I am trying to do with this list is remove the decimal and have the results end up padding with zeros to make a fixed length of 6 char and where the decimal is always implied between the 2nd and 3rd char, so I have to left pad and right pad with zeros to make all output 6 characters.

Thank you so much in advance!!

 

[L. Howard]

Hi john69,

A bit clunky but try this with your data in Column A.

In B1 =A1&"000" and pull down.

In C1 =0&LEFT(B1,1)&MID(B1,FIND(".",B1)+1,4) and pull down.

Regards,
Howard

 

[Peter_SSs]

You don't need a vba function, but I have provided one as well as a standard formula.

Excel Workbook
	A	B	C
1	0.1845	001845	001845
2	1.054	010540	010540
3	1.16	011600	011600
4	1.116	011160	011160
5	0.278	002780	002780
6	0.91	009100	009100
Reformat Number

Function myFormat(s As Double) As String
  myFormat = Replace(Format(s, "00.0000"), ".", "", 1, 1, 1)
End Function

 

[john69]

Hello L. Howard. You're right, it is a bit clunky, but it does exactly what I was trying to accomplish! Thank you for your quick response

 

[john69]

Thank you Peter_SSs - this one takes one step out of the equation!!