Solved

# suffix 0 forumula

Posted on 2014-10-02
86 Views
I need a forumula to add a 0 to some text, i.e. in A1 I want the contents of B1 with a 0 in front so if B1 contains 7869, A1 should become 07869. Unfortunately these are not all fixed length so I cant format the fields. Also I some of the entries in column B are blank, in those cases I want A1 to stay blank, and not simply show 0. Any pointers?
0
Question by:pma111
• 2

LVL 48

Accepted Solution

Rgonzo1971 earned 167 total points
ID: 40356615
Hi,

pls try

=IF(B1<>"","0"&B1,"")

Regards
0

LVL 31

Assisted Solution

Rob Henson earned 333 total points
ID: 40356617
Try this:

=IF(A1="","","0"&TEXT(A1,REPT("0",LEN(A1)))

Thanks
Rob H
0

LVL 31

Assisted Solution

Rob Henson earned 333 total points
ID: 40356626
Or:

=IF(A1="","",TEXT(A1,REPT("0",LEN(A1)+1))

Thanks
Rob H
0

LVL 13

Expert Comment

ID: 40356651
If it's only for numbers to display, you could do that with the formatting.

Open cell formatting dialog, tab numbers, click on "User defined format" and enter 00000 as format definition.

That will add as much zeroes to the beginning onf the numer as needed to reach the given number of digits in the formt definition. 1 will be displayed as 00001, 11 will be displayed as 00011 and so on. If the length of the number equals or exceeds the length of the format string, the the full number will be displayed w/o any zeroes.
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…