Adding a Specific Number of Zeros To A Material Number

[SonicBoomGolf]

I am making a small macro to convert materials outside of SAP into materials that can be recognized by SAP. Here is a sample material transition I am looking to do.

Convert material 1031560 to 000000000001031560

Obviously, outside of SAP those extra zeros have no value so I remove them. However, sometime I have to add them back in when I want to query specific materials in BW.

The code below is getting me very close to where I want to go. All I need to figure out is how to add in a specific number of zeros to the front of a material number.

In the example above, I would have to add 11 zeros and format the number as text in order to get a legit 18 digit material number (in SAP, all our materials are 18 digits long).

Sample material numbers are in column A and the number of characters in the current material are identified in column b. How can I add in the number of zeros needed as I describe above? Thanks for the help in advance.

Sub SAPMaterial()
Dim x As Integer, rng As Range, c As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & LastRow)
For Each c In rng.Cells
'Materials in SAP are 18 characters long, so 18-number of characters in current material number will identify how many zeros I have to add to get a legit material.
c.Offset(0, 1).Formula = "=len(" & c.Address & ")"
'Not sure what to do here to add in the zeros needed to get to a legit 18 digit material
Next c
End Sub

 

[CyberGuy2004]

One of my many mottos is, "Never send a macro to do a formula's job." Unless you have a really good reason for a macro in this instance, this formula will work.

value 1031560 in A2
formula in whatever cell you choose:

=RIGHT("000000000000000000"&FIXED(A2,0,TRUE),18)

The formula transforms your material number to alpha, with no decimals and no commas, then concatenates 18 zeroes to the front. Finally it selects the rightmost 18 characters.

 

[Scott Huish]

I was writing a macro to insert the formula, but I was using this which I think would suffice:

=RIGHT("000000000000000000"&A1,18)

Sub SAPMaterial()
Dim LastRow As Long, rng As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("B1:B" & LastRow)
rng.Formula = "=RIGHT(" & Chr(34) & "000000000000000000" & Chr(34) & "&A1,18)"
End Sub

 

[SonicBoomGolf]

Excellent call. This formula will work perfectly. Thanks to both of you.

 

[facethegod]

Question:

Is there a reason you don't just want to use a formula:

=Text(A1,rept(0,11))

with this you can specify how many zeroe's you need

 

[Scott Huish]

Good call.

With that you would want 18:

=TEXT(A1,REPT(0,18))

or you could use 18 zeros:

=TEXT(A1,"000000000000000000")

 

[jon_k]

Looking at this problem, I would have suggested

=text(A1,"000000000000000000")

Just out of interest, does text take considerably more processing time?

I'm stuck with a couple of really slow spreadsheets and was wondering if there's a way to improve performance