PowerPivot - Padding with Zeros

[ShaunP]

I have a column of project numbers that vary in length. My plan is to write an IF statement to check the length of the Project Number and pad these numbers with 0 so that the formats are the same, max length of 10, if needed.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Current Project Number[/TD]
[TD]New Proj Num[/TD]
[/TR]
[TR]
[TD]PRJ3[/TD]
[TD]PRJ0000003[/TD]
[/TR]
[TR]
[TD]PRJ0000012[/TD]
[TD]PRJ0000012[/TD]
[/TR]
</tbody>[/TABLE]

I'm using the MID & Format functions to find the location to start padding but it's not working for me.

=FORMAT(MID([ProjectNumber],4,len([ProjectNumber])),"0000000")

Using the code above will give a result of 3 and 0000012 for the 2 examples above.

I'm using the PowerPivot plugin for Excel 2010 (version 11.0.5058.0). I appreciate the help!

 

[VBA Geek]

try:

=<br><span class="Keyword" style="color:#0070FF">LEFT</span><span class="Parenthesis" style="color:#969696"> (</span> [Current Project Number], <span class="Number" style="color:#EE7F18">3</span> <span class="Parenthesis" style="color:#969696">)</span><br>    & <span class="Keyword" style="color:#0070FF">REPT</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="StringLiteral" style="color:#D93124">"0"</span>, <span class="Number" style="color:#EE7F18">10</span> - <span class="Keyword" style="color:#0070FF">LEN</span><span class="Parenthesis" style="color:#969696"> (</span> [Current Project Number] <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span><br>    & <span class="Keyword" style="color:#0070FF">MID</span><span class="Parenthesis" style="color:#969696"> (</span> [Current Project Number], <span class="Number" style="color:#EE7F18">4</span>, <span class="Number" style="color:#EE7F18">7</span> <span class="Parenthesis" style="color:#969696">)</span><br>

 

[ShaunP]

try:

=
LEFT ( [Current Project Number], 3 )
& REPT ( "0", 10 - LEN ( [Current Project Number] ) )
& MID ( [Current Project Number], 4, 7 )

Thanks VBA Geek but that did not work. I get an error stating the REPT is the wrong data type. Both columns are formatted at Text.

 

[ShaunP]

Looks like the REPT formula does not like this section 10 - LEN ( [Current Project Number] )

 

[VBA Geek]

Looks like the REPT formula does not like this section 10 - LEN ( [Current Project Number] )

If you have project numbers with a len higher than 10 then you may get an error. is it the case_?

 

[ShaunP]

If you have project numbers with a len higher than 10 then you may get an error. is it the case_?

Thank you, yes that was the problem.