Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

suffix 0 forumula

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
pma111
Asked:
pma111
  • 2
3 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

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

Regards
0
 
Rob HensonIT & Database AssistantCommented:
Try this:

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

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Or:

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

Thanks
Rob H
0
 
frankhelkCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now