Adding a zero in front of a number
How to add a zeros in front of a number when it starts with a nine
Anwsers to the Problem Adding a zero in front of a number
I was looking at something like this.
The problem is: that might presume too much about the length of the value in B4. More simply:
=IF(LEFT(B4,1)="9","0" & B4,B4 & "")
The B4&"" expression ensures that the formula always returns text, which "0"&B4 does. It is "good practice" to return a consistent result. However, if B4 is text to begin with -- that is, TYPE(B4) returns 2 -- you can replace B4&"" with simply B4.
If B4 is not text to begin with -- that is, TYPE(B4) returns 1 -- there might some question as to whether the result of the formula above should be numeric or text.
Only you can answer that question. It depends on how you intend to use the result.
If you want a numeric result and if your numbers starting with 9 are always 6 digits, as your examples and your TEXT formula suggests, you might be able to use the following Custom formula in B4 or in a cell who formula is =B4:
EDIT.... Oh, but when Tushar suggested using XL2007 Conditional Formatting to do effectively the same (his CF is even more flexible), you responded: "I was thinking of using an IF statement instead".
In that case, the Custom format above might be equally undesirable. But in that case, I have to ask: why use an IF statement when formatting of one form or another would accomplish what you require?
PS: It should be noted that no number format will help you if B4 is text -- unless you "convert" B4 to numeric with a formula like =--B4. I don't believe you ever said what the type of the value in B4 is.
How to Avoid Downloading Malware
Where are you getting the download?
There are malicious people who download valid copies of a popular download, modify the file with malicious software, and then upload the file with the same name. Make sure you are downloading from the developer's web page or a reputable company.
Cancel or deny any automatic download
Some sites may automatically try start a download or give the appearance that something needs to be installed or updated before the site or video can be seen. Never accept or install anything from any site unless you know what is downloading.
Avoid advertisements on download pages
To help make money and pay for the bandwidth costs of supplying free the software, the final download page may have ads. Watch out for anything that looks like advertisements on the download page. Many advertisers try to trick viewers into clicking an ad with phrases like "Download Now", "Start Download", or "Continue" and that ad may open a separate download.
Another Safe way to Fix the Problem: Adding a zero in front of a number:
How to Fix Adding a zero in front of a number with SmartPCFixer?
1. You can Download Error Fixer here. Install it on your system. When you open SmartPCFixer, it will perform a scan.
2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.
3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,How to Fix Error - Getting an error "not connected to the internet" while trying to install Samsung Kies?
,How to Fix - Internet Explorer shuts down and reopens tab when attaching to email or uploading files.?
,Fast Solution to Problem: Sending Error Message
,[Anwsered] Thinkpad 8611 Boot,How to Resolve - Svchost Helper?,Fast Solution to Problem: L30 101 Driver Windows 7,Troubleshooter of Error: Io Device,How to Fix Error - Dell Laptop Code 39?
Read More: Advice for PivotTable,adobe flash problems, I'm running 64 bit. Tech Support,Troubleshoot:Adding Cell Values to a Macro when sending an email,How to Resolve - Address Bar in IE 8 in Windows XP?,How to Fix Problem - Aero Peek being black?,a file called mDNSResponse.exe. is causing bonjour not to operate properly,what should I do?,A QUESTION USING THE "IF'S" Formula.,A continuos flashing window with which title is C:Windows\System32\cmd.exe, and has the following message: The syntax of the command is incorrect.,Acrobat compatibility issue and you tube problems____,ActiveX on IE 9 not loaded