Solved

suffix 0 forumula

Posted on 2014-10-02
4
86 Views
Last Modified: 2014-10-02
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
Comment
Question by:pma111
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 167 total points
ID: 40356615
Hi,

pls try

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

Regards
0
 
LVL 31

Assisted Solution

by:Rob Henson
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

by:Rob Henson
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

by:frankhelk
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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,…
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now