Custom Cell Formatting in Excel – Few Tips & Tricks

Many Thanks. This is really informative. I want to know how a program can read”contents” of a Custom cell. WRT the eg. above, if the Value After is 0001, when a program reads the value, it reads it as 1 instead of 0001.

Thanks. So in case, I want my program to pick the value the way it appears on the UI, how can that be done?

I found your blog via Google while searching for free personal finance tips and your post regarding use custom cell formats in Microsoft Excel – tips | Pointy Haired Dilbert – Chandoo.org looks very interesting to me. I always enjoy coming to this site because you offer great tips and advice for people like me who can always use a few good pointers. I will be getting my friends to pop around fairly soon.

Dear Sir,
I also have some problem in excel, can you solve my problem. I gave you my email ID .

Thanking you

I’ve been browsing this thread for awhile and there is one format I can’t seem to create a custom format for. If I want to go from 1234-5678 to 12345678 how can I do that?
Thanks!

tour

i want to calculate formula for example: 500PCS X 12 = 6000. but when i mention PCS or doz or set after any digit as above, formula says #VALUE. It can not give result.
suppose 500PCS IN A CELL x 12 IS IN B CELL FORMULA IS =SUM(A1*B1). kindly help me abt.

Asghar,

You can try using the left function. Using your example:
=(LEFT(A1,3)*B1) ought to give you 6000.

Thus said, you need to customize this formula in case the number of units run into thousands.

Chandoo,
I am enjoying my first visit to your site. Looking at the thread posted by KC, you are absolutely right. Substitute function is the way to go about it.

Considering 1234-5678 is in cell A1, the substitute function will be:
=SUBSTITUTE(A1,”-”,”",1)

Lots of good stuff in here!!! Makes me feel nostalgic about my good old college days.

I have a problem when formatting a cell that has 16 or more digits, using the Custom format. We are working with Credit Card numbers and want to be able to enter them as a 16 digit number like this: 1234123412341234 but want them to look like this: 1234 1234 1234 1234
The problem we’re having is that the last digit always turns into a 0 (zero) and the number ends up looking like this: 1234 1234 1234 1230
The custom format type we created is: 0000 0000 0000 0000 but we’ve also tried: #### #### #### #### with the same results.
This only happens when using 16 or more digits. If we use 15 or less digits, the Custom format works fine. Unfortunately, credit cards have 16 digits so we’re stuck with the problem.
We’ve tried this on Excel 2007 with service pack 1 and on Excel 2000 with service pack 3 with the same results. The Operating Systems we’ve tried it on are Windows XP with Service Pack 3 (and all other Microsoft Updates installed) and on Windows 2000 with Service Pack 4.
Any help would be greatly appreciated.

Hi Chandoo –
Awesome suggestions…. big question for ya! We have P.O. numbers that come from a system with the standard format of XX-XXXXX. The first 2 XX are the year – the very last X could be there or not be there depending on whether we are at the beginning of the year or the end of the year. When we dump data from this system into excel, 95% of the PO numbers format correctly in the column: 09-1234 or 09-12345 for example.

The other 5% show as a date in the field: Sep-67. If you place your cursor in the formula bar it says 9/1/8267. The REAL PO number for that one is 09-8267. I’ve tried a ton of different ways to try and make a custom format for the user and am coming up blank every time.

Any help would be of tremendous help! Thanks!
SReid

Hi Chandoo – Sorry no luck on that format. The correct PO number is 08-8979. When dumped into Excel, and reviewing it the cell – it changed to a date format of Aug-79. If looking at it in the formula bar, it is 8/1/8979. If I apply the custom format you suggested, it changes the PO number in the cell to 25-85766 and in the formula bar to 2585766 (without the dash). Crazy I tell you! Crazy!

Thanks! SReid

hi chandoo,

hope you can help me with this problem; it’s something about number formatting and “Creating dash boards using excel conditional formatting” @ http://snipurl.com/g2rwb

consider these data:

set 1: 2851/3.00 = 0.001052262…… = 0.11% (% with 2 decimal)
set 2: 2851/3.01 = 0.00105576….. = 0.11% (% with 2 decimal)

when i do a IF comparison for both 0.11%, it’s NOT EQUAL. This creates a little problem with the dashboard formula whereby it will show icon ? instead of ?.

The only way to ’solve’ this is to force format each cell with this: =text(2851/3.00,”0.00%”) and =text(2851/3.01,”0.00%”). Only then that bot sets are EQUAL.

As there a simpler way to TRULY HARD FIX the decimal numbers in a percentage format? 0.11% == 0.11%

Thanks!

rewrite:

This creates a little problem with the dashboard formula whereby it will show icon “^” instead of “o”.

cybpsych,

use ROUND(2851/3.00,4) and ROUND(2851/3.01,4) instead and the two values will be equal.

Hi CHandoo….
I am dumping the data from SQL. It is a varchar field. I have tried setting the column to TEXT. This does not work. SQL has the field (correctly) as 08-8979. When dumped to Excel, it changes it to: Aug-79. If I try to change the format to TEXT, the data then CHANGES to: 25-85766.

The crazy part of this is that it only happens on about 5% of the data in that column. All of the rest of the data comes over in the correct XX-XXXXX format.

Any other ideas?

Thanks!

hi chandoo, i’m curious on the following:

how to custom format this number: 883509615 –> 883.509615 Million or 0.883509615 Billion?

don’t bother on the decimal numbers; I can round them up as per Robert’s suggestion above

thanks!

cybpsych,

use the thousand operator in custom number format:

Assumed you have the cell value 12345678.

Custom format #,###.00, will display 12,345.68
Custom format #,###.00,, will display 12.35
Custom format #,###.00,,, will display 0.01

thanks robert for your help

Hey Chandoo,

I’ve tried relentlessly to figure this one out, but I’ve given up at this point. I have a sheet full of millions of entries that I input onto a worksheet, which are then pulled into a more intricate report using vlookup. Within one of the columns is a UPC code that i’m trying to transfer, and it should contain 12 digits (000000000000 custom number format). I have changed the columns format to suit that, so that some of the columns that had 11 digits will now have 12. However, even though I have changed the format, the 11 digit numbers remain unchanged until i open the formula cell and click into another cell. I obviously can not do this to the 100’s of entries every week….do you have any suggestions?! thanks!

hi chandoo/john,

i believe this is happening because the data is imported from other sources?

if so, Excel doesn’t treat the “numbers” as numbers. You will need to convert them into proper numbers in Excel.

If AutoCorrect is on, select the range and hover aroudn the GREEN corner arrow. Select the “Covert to Number”.

If I wanted all cells within column A to only have 7 digit numbers how can this be done? If a user wanted to enter 12, that 12 would be converted to: 0000012. I basically only want values: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9 and locations 1, 2, 3, 4, 5, 6, and 7 of the 7 digit string. I’ve been fooling with some time with no lock. I first converted the entire column A to text and gave it a format of 0000000 so that it got preceding zeros when values that are less than 7 digits are given. Then I started fooling with the Data Validaion putting: =LEN($A$1)=7

but it doesn’t seem to work quite right.

Hello !! I am facing a problem in displaying value in this format “1/1″ (Without inverted commas).

Currently I have information in this form 1 / 1. When I remove spaces from the cell, the value is automatically converted to Date Format as 1-Jan. Can anyone of you help me getting the required format after removing spaces.

Many Thanks in advance !

Making the problem more clear !

Current Value of the cell: 1 / 1

After removal of space from the cell

New Value of the cell: 1-Jan

Required Format: 1/1 (without space)

An urgent response will be highly appreciated !! Thanks !!

@Messam there’s a method that comes from Lotus 1-2-3. When you’re typing the value, start with a ‘ (apostrophe). So, in your example, typing ‘1/1 should work properly.

Hope that helps.

Many Thanks !!

I got a solution myself as well.

The same can be done using a function in excel called “SUBSTITUTE”.

Aires: I could not understand the solution you provided. Can you please explain?!?!

re: John M

I am having the exact same problem as you! Did anyone ever find a solution??

I had tried everything I could think of for the problem John described that I was also having.
I had this issue with phone numbers and zip codes.
I decided to use text to columns to split up my zip+4 column and accidentally clicked finish on the first step (with delimited selected) and magically my entire column of data updated to display the correct format. I repeated my “mistake” on the phone number column, which was also not updating to the correct format, and it worked there too!
It’s a silly way to have to do it, but hey, whatever works…!
I would love to know what is causing this issue though.

I have a spreadsheet of X’s and I want to add the number of Xs in each column. I’d like to custom fomat X so that it reads as 1 and then I can add all the 1s. How would I do that?