Custom number formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with tariff numbers in a column already in the following
format: 9018.90.8000 and I want to be able to:

1. eliminate the "periods" in the current column
2. once I enter new tariffs have the column formatted (line you can social
security or phone numbers)

Can anyone help me...please.

Lonnie
 
Periods are the dots in the number sequence the OP gives.

As to removing them, =REPLACE(A1,".","") will eliminate them.

I don't understand the second question.

Dave
 
Dave,
answered than questions that provide no detail about your problem.

Totally agree.

Let me guess. Does the OP mean to say

once I enter new tariffs have the column formatted (like (instead of line) you can *with* social
security or phone numbers)

For my learning purpose, I use custom format ####"."##"."####

I keyed in the digits without the periods and the format took place automatically.

Yes, we can use format>cells>number>special for social security or phone numbers but *not* for tariff (codes).

Dave, if there is a better way than my suggested solution, please let me know.

Epinn

Periods are the dots in the number sequence the OP gives.

As to removing them, =REPLACE(A1,".","") will eliminate them.

I don't understand the second question.

Dave
 
Epinn--yes, if you want to use the custom format you suggest, that would
work. However, my understanding of the OP's question was that he wanted to
eliminate the periods, not include them.

Perhaps I'm just very dense and I am not understanding a simple question!

Dave
 
Thanks, Dave. I just remember that there is a better way to do it. I can save some keystrokes by using the \ instead of ""

####\.##\.####

My interpretation of the OP's request is as follows:-

Currently the periods shown are the result of typing/keying. He/she wants to get rid of them first and then use custom formatting to insert the periods.

It's a guessing game and it has nothing to do with comprehension. <G> The OP may want something totally different from what I think. We'll never find out unless he/she gives us some feedback.

Epinn

Epinn--yes, if you want to use the custom format you suggest, that would
work. However, my understanding of the OP's question was that he wanted to
eliminate the periods, not include them.

Perhaps I'm just very dense and I am not understanding a simple question!

Dave
 
Thanks all for responding...sorry if my orginal question was
confusing.....let me try again.
In column E I have a list of numbers in the following format: 9018.90.8000
I would like to write a formula or format that will get rid of the periods
in this column without having to retype each number without the periods.

Thanks,

Lonnie
 
Thanks for the feedback.

Dave wrote:

....... I am not understanding a simple question!

Epinn wrote:

.......The OP may want something totally different from what I think.

Dave, you understood perfectly whereas I had a more vivid imagination. ;)

I just thought of something. Would appreciate your insight.

Method 1

The OP can click on one cell and do format>cells>number and see if a category other than general, number or text is highlighted. If, say, custom is highlighted, he/she can just remove the format for the entire column E.

Method 2

In F2, type the formula =SUBSTITUTE(E2,".","") and drag it down as far as needed.

However, there appears to be a difference in the format of the end result.

Method 1 - number
Method 2 - text

Wonder what you think.

Epinn

Thanks all for responding...sorry if my orginal question was
confusing.....let me try again.
In column E I have a list of numbers in the following format: 9018.90.8000
I would like to write a formula or format that will get rid of the periods
in this column without having to retype each number without the periods.

Thanks,

Lonnie
 
Epinn,

you can make it return a number by putting VALUE( ) around your
formula, or 0 + , as in:

=VALUE(SUBSTITUTE(E2,".",""))

=0 + SUBSTITUTE(E2,".","")

as long as there are no letters in the original.

Hope this helps.

Pete
 
Pete,

Thanks. Yes, I remember VALUE().

I found something "interesting."

Scenario 1 I type in the periods. 1234.56.7890

Scenario 2 I type in the numbers only (1234567890) and then use custom format ####\.##\.####

=SUBSTITUTE(E2,".","") returns text format for both. The period is removed.

=VALUE(SUBSTITUTE(E2,".","")) returns number format for both. The period is removed.

So far so good. Here is the interesting part.

Scenario 1

=0+(SUBSTITUTE(E2,".","")) returns number format (right aligned) and the period is removed.

Scenario 2

=0+(SUBSTITUTE(E2,".","")) does *not* remove the period but it is right aligned.

Trying to figure out what Excel is telling me. Another anomaly? Safer to use VALUE than 0+? Appreciate input from all.

Epinn

Epinn,

you can make it return a number by putting VALUE( ) around your
formula, or 0 + , as in:

=VALUE(SUBSTITUTE(E2,".",""))

=0 + SUBSTITUTE(E2,".","")

as long as there are no letters in the original.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Raspberry Pi 2 energy monitor with Inky pHAT (ePaper/eInk/EPD). 2
format a column 3
Excel VBA to move cursor from 1 cell to another, once original cell is filled with data. 3
return value other than #N/A 2
EE prices first LTE phones and tariffs in the UK 6
formatting a spreadsheet help 4
Format numbers as "Number" 6
Format Cells - Custom 1

Back
Top