| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom number formatting
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
Lonnie,
Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
Dave,
I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Epinn" wrote: Dave, I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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. ...... I am not understanding a simple question! 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Epinn" wrote: Dave, I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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" wrote: 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. ...... I am not understanding a simple question! 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Epinn" wrote: Dave, I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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 formatcellsnumber 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 "Lonnie" wrote in message ... 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" wrote: 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. ...... I am not understanding a simple question! 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Epinn" wrote: Dave, I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in message ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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 On Feb 5, 10:39 pm, "Epinn" wrote: 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 formatcellsnumber 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 "Lonnie" wrote in ... 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" wrote: 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. ...... I am not understanding a simple question! 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 "Dave F" wrote in ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Epinn" wrote: Dave, I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom number formatting
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 "Pete_UK" wrote in message oups.com... 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 On Feb 5, 10:39 pm, "Epinn" wrote: 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 formatcellsnumber 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 "Lonnie" wrote in ... 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" wrote: 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. ...... I am not understanding a simple question! 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 "Dave F" wrote in ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Epinn" wrote: Dave, I don't understand the second question. A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. Totally agree. once I enter new tariffs have the column formatted (line you can social security or phone numbers) 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 formatcellsnumberspecial 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 "Dave F" wrote in ... 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Lonnie, Can you please explain What is Period??? Mujeeb "Lonnie" wrote: 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- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Want to custom formatting a number similar to @,@ for text. | Excel Discussion (Misc queries) | |||
| Custom Number Format | Excel Discussion (Misc queries) | |||
| Custom number formats | Excel Worksheet Functions | |||
| Auto Formatting Custom Number | Excel Discussion (Misc queries) | |||
| Pivot Table Drill Down Number Formatting | Excel Discussion (Misc queries) | |||
Linear Mode
