Add leading Zero

building reports webintelligence
1

Hi all,

I want to format a column and add leading zero for one numbers
Example:

If it 25779 then I need to see 025779

RUC :us: (BOB member since 2010-05-03)

2

is it crucial that the number be a number and not a string? If not, just concat a 0 on the front.

rbacon :us: (BOB member since 2011-02-03)

3

This may seem a silly question, but is the field a string? If so, you can use concatenation…

sredden1 :us: (BOB member since 2011-06-09)

4

You can create in Variable Editor a new Dimension

in the Formula you write … .
= Concatenation ( “0” ; [yourTable_Number_Object] )

than parse and if ok ready to use … :slight_smile: .

Werner_A :austria: (BOB member since 2012-01-24)

5

Will it work if the number changes dynamically.

RUC :us: (BOB member since 2010-05-03)

6

I do this a lot, in my universe and even in SQL for fields which look numeric but are not.

Remember “0123” is not a number. It is a text string.
We use it a lot however in things like “Purchase Order Number” or “Invoice Number” where people expect the “number” to be a specific set of digits.

In MS SQL (Raw in database, or In Universe Designer) I use a function similar to:

Right('000000' + Cast(123 as varchar(6)),6)

Replacing 123 with whatever column or object I need.
That forces the 123 to both be a text object, and ensures a 6 digit size… even if the number was 12345, it’d come out as ‘012345’.

I’m sure you can use a similar function in WEBI Directly, the only difference is you don’t have the Cast function, but I believe WEBi will auto cast it for you.
Try:

=Right("0000" + [SomeNumericObject];4)

I am pretty sure that will work just fine, though you might get odd results if the number object can end up being null.

JPetlev (BOB member since 2006-11-01)

7

I believe the question was for formatting the number:

If you have a set number of digits, you can make a custom numeric display. For instance, a 5 digit number would have the format of: 00000

If you need to have it variable length, you can replace those with 9.
For a number that you always want a zero, up to 5 digits, something like: 99990

digpen :us: (BOB member since 2002-08-15)

8

Wow did we ever miss the point on that one… hah. You’re correct if you just want to Format the number, then Custom Formatting of “00000” would always display the results with 5 digits.

However, “99990” actually would show 4 9’s then your number…
ie if your number was 3456, you’d get 99993456 , not what he would want :slight_smile:

I think you meant to say #0, or even #,##0 (to include a comma).

Either way though all of our solutions were way over the top when he just wanted something formatted… not sure what we were thinking of.

JPetlev (BOB member since 2006-11-01)

9

I think the padleft function was actually created for that exact purpose.

Peter

pderop (BOB member since 2010-10-27)

10

I am having a similar problem, but I need to store it as a number rather than just display it. I am trying to store 0801 as a number. When i use the 0000 formatting, excel still stores it as 801. The number is being fed into a prompt for live office and the prompt can only read numbers so changing the cell to text also doesn’t work. is there a way to actually have excel store the value 0801 as a number, or am i out of luck?

HelpMePlease (BOB member since 2013-03-01)

11

Well 0801 is not a number :slight_smile: That’s why you’re having an issue.
0801 is text
801 is a number
Numbers do not have leading zeros.
Otherwise you couldn’t tell the difference between 0801 and 00000801.

If you want to FORMAT something as text, that’s not the same as changing it’s value.
To force the system to change the value , you can use code similar to:
=RIGHT(“000” + ;4)
That will get the right 4 characters and leave the result as text…

If you prompt in Live Office can only read numbers, then 801 is what you want to feed it however, not 0801.

JPetlev (BOB member since 2006-11-01)