Custom Number Format Formula

**rosethorn5** · 08-03-2015, 03:49 AM

Hello,

I am working on an excel spreadsheet for work and I need help creating a custom formula. At work we use cost codes in order to hide our cost from the customers. It would be very helpful to be able to enter our cost in standard currency format (i.e., 195; 11; 12.99; 0.75), and have it automatically convert to our cost code format (i.e., 195; 11; 1299; 0075). Can someone please help me find the appropriate Public Function that I need in order to create my custom formula, as well as help me with how to write the custom formula??

I need there to be zeros where there is a tens place if it is a single digit number, and I need two zeros for the tens and ones place if it is cents only. If it is a whole, two-digit or three-digit number then it can stay as is. If there is a decimal place, then I need the decimal removed. Below are examples of how I would need the codes to be formatted:

.45 = 0045
.50 = 0050
5 = 05
4.95 = 0495
11 = 11
11.50 = 1150
250 = 250
199.95 = 19995
1000 = 1000
1000.05 = 100005

Is there a way to create a formula to satisfy these conditions?? I would really appreciate it!

Thank you!!

**JohnTopley** · 08-03-2015, 04:09 AM

Try ...

Data in A1

=IF(MOD(A1,1)=0,TEXT(A1,"0"),TEXT(A1*100,"0000"))

**rosethorn5** · 08-03-2015, 04:39 AM

Thank you. I tried it, but when I entered it exactly, it showed up in the cell as the formula, not as a numberical value. :-/

**JohnTopley** · 08-03-2015, 04:40 AM

The cell you entered the formula in was formatted as text: format as general.

**rosethorn5** · 08-03-2015, 04:48 AM

You're totally right! The formula worked perfectly as I need it to, thank you SO MUCH!!

**JohnTopley** · 08-03-2015, 04:51 AM

Glad it's sorted. Can you mark this as SOLVED.

**rosethorn5** · 08-03-2015, 04:56 AM

I sure can. This is my first time on this. Will you tell me where I can mark it as solved?

**Sam Capricci** · 08-03-2015, 05:06 AM

at the top of the post is a dropdown section titled thread tools, use the dropdown and select solved there. And don't forget to give John a bump to his reputation for helping you. He'll appreciate it.

**JohnTopley** · 08-03-2015, 05:17 AM

@Sambo Kid: thank you. Not too bothered about the "reputation" - for me a simple thank you (if offered) is fine!

**rosethorn5** · 08-04-2015, 02:26 AM

Originally Posted by JohnTopley

Try ...

Data in A1

=IF(MOD(A1,1)=0,TEXT(A1,"0"),TEXT(A1*100,"0000"))

Hello, this formula works perfectly, with the exception of when using single digit whole numbers. Otherwise, every other type of number is exactly how I need it to be. Below are examples of how I need each type of number to be formatted into. In parenthesis I've notated which one is not being formatted the way I need it to be:

.45 = 0045
.50 = 0050
5 = 05 (shows up as 5 instead of 05)
4.95 = 0495
11 = 11
11.50 = 1150
250 = 250
199.95 = 19995
1000 = 1000
1000.05 = 100005

**JohnTopley** · 08-04-2015, 02:51 AM

See next response.

**JohnTopley** · 08-04-2015, 02:53 AM

Sorry .. Ignore my last post ! my mistake ... change to

=IF(MOD(A1,1)=0,TEXT(A1,"00"),TEXT(A1*100,"0000"))

0.45 0045
0.5 0050
5 05
4.95 0495
11 11
11.5 1150
250 250
199.95 19995
1000 1000
1000.05 100005

**rosethorn5** · 08-04-2015, 03:09 AM

YES PERFECT!!! Thank you soooo much!!

**JohnTopley** · 08-04-2015, 09:25 AM

Apologies for the error: I should engage brain before responding!

Custom Number Format Formula

LinkBack

Thread Tools

Rate This Thread

Display

Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Re: Custom Number Format Formula

Thread Information

Users Browsing this Thread

Similar Threads

Need formula or code to convert number(s) from custom format to number format

Custom Number Format

Conveying custom number format to new cell via formula

custom number format in formula

how do I add phone number format as a permanent custom format?

Format a cell with a custom number format

Custom number format always defaults last number to 0.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions