Converting amounts to fixed-length

[Marcster]

In column C I have amounts like:
350.62
64.63
165.06
92.83
374.87
69.21
45.83
333.12
454.03
334.3

Which I need converting to:

00000035062
00000006463
00000016506
00000009283
00000037487
00000006921
00000004583
00000033312
00000045403
00000033430 - this one was 334.3 (need the zero on the end in the conversion)

The amounts should be formatted so that they are 11 characters in length
without any decimal places in and zeros at the front to make up the length.
How many amounts in column A will vary.

Can anyone tell me a VBA macro in which do this please?.

Thanks,

Marcster.

[MWE]

In column C I have amounts like:
350.62
64.63
165.06
92.83
374.87
69.21
45.83
333.12
454.03
334.3

Which I need converting to:

00000035062
00000006463
00000016506
00000009283
00000037487
00000006921
00000004583
00000033312
00000045403
00000033430 - this one was 334.3 (need the zero on the end in the conversion)

The amounts should be formatted so that they are 11 characters in length
without any decimal places in and zeros at the front to make up the length.
How many amounts in column A will vary.

Can anyone tell me a VBA macro in which do this please?.

Thanks,

Marcster.

this could be done without VBA. It would be a bit complicated, but could be done. However, since you asked about VBA, see the attached file. Select the target cells and click on the button

[mvidas]

Hi Marcster,

First off,you don't need to use VBA for this.. You can enter the following formula for the cells you need, then use copy/paste special/values:

=TEXT(C1*100,"00000000000")

But if you really want a vba solution,

Dim TheColumn As Range
Set TheColumn = Columns("C")
TheColumn.Offset(0, 1).Insert
 With Range(TheColumn.Cells(1), TheColumn.Cells(65536).End(xlUp))
  .Offset(0, 1).FormulaR1C1 = "=TEXT(RC[-1]*100,""00000000000"")"
  .Offset(0, 1).Copy
  .PasteSpecial xlPasteValues
  .Offset(0, 1).EntireColumn.Delete
 End With

Matt

[MWE]

Hi Marcster,

First off,you don't need to use VBA for this.. You can enter the following formula for the cells you need, then use copy/paste special/values:

=TEXT(C1*100,"00000000000")

But if you really want a vba solution,

Dim TheColumn As Range
Set TheColumn = Columns("C")
TheColumn.Offset(0, 1).Insert
 With Range(TheColumn.Cells(1), TheColumn.Cells(65536).End(xlUp))
  .Offset(0, 1).FormulaR1C1 = "=TEXT(RC[-1]*100,""00000000000"")"
  .Offset(0, 1).Copy
  .PasteSpecial xlPasteValues
  .Offset(0, 1).EntireColumn.Delete
 End With

]Matt

certainly much more elegant solutions than mine. I wonder why I did not think to multipjy by 100 to get rid of the decimal point and solve the lack of hundredths place problem. Must be getting old. Or maybe it was that 30 foot branch that almost fell on me an hour ago? Yes, I wil blame it on that.

[mvidas]

I'm not surprised with that wind out there! Random bouts of torrential rain and 40-50 mph wind.. yeah we both chose the right place to live

[Marcster]

Thanks for the replies ,
Now does what I want, thanks again.

Marcster