Inserting dashes to an existing number

Hi,

I am trying to create a formula that will divide this ten digit numbe
into the following dashes.

Here is the number 1234000999  I need the dashes to be inserted in thi
format 1234-000-999.


What is the appropriate formula.  

Thanks for your help.
Dinahros

--
dinahros
-----------------------------------------------------------------------
dinahrose's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1334
View this thread: http://www.excelforum.com/showthread.php?threadid=26643

0
10/5/2004 1:45:56 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
350 Views

Similar Articles

[PageSpeed] 19

Why not just custom format
right click on selection>format>number>custom>
####-###-###

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"dinahrose" <dinahrose.1dnotc@excelforum-nospam.com> wrote in message
news:dinahrose.1dnotc@excelforum-nospam.com...
>
> Hi,
>
> I am trying to create a formula that will divide this ten digit number
> into the following dashes.
>
> Here is the number 1234000999  I need the dashes to be inserted in this
> format 1234-000-999.
>
>
> What is the appropriate formula.
>
> Thanks for your help.
> Dinahrose
>
>
> -- 
> dinahrose
> ------------------------------------------------------------------------
> dinahrose's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=13340
> View this thread: http://www.excelforum.com/showthread.php?threadid=266437
>


0
Don
10/5/2004 2:03:17 PM
Hi
in addition to Don if you need a formula try:
=TEXT(A1,"0000-000-000")
in an adjacent cell

--
Regards
Frank Kabel
Frankfurt, Germany

"dinahrose" <dinahrose.1dnotc@excelforum-nospam.com> schrieb im
Newsbeitrag news:dinahrose.1dnotc@excelforum-nospam.com...
>
> Hi,
>
> I am trying to create a formula that will divide this ten digit
number
> into the following dashes.
>
> Here is the number 1234000999  I need the dashes to be inserted in
this
> format 1234-000-999.
>
>
> What is the appropriate formula.
>
> Thanks for your help.
> Dinahrose
>
>
> --
> dinahrose
> ---------------------------------------------------------------------
---
> dinahrose's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=13340
> View this thread:
http://www.excelforum.com/showthread.php?threadid=266437
>

0
frank.kabel (11126)
10/5/2004 2:07:48 PM
Custom format is the simplest but if you really need to add the - the
try this

=LEFT(a1,4)&+"-"&+MID(a1,5,3)&+"-"&+RIGHT(a1,3

--
Alex Delamai
-----------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127
View this thread: http://www.excelforum.com/showthread.php?threadid=26643

0
10/5/2004 2:14:37 PM
Reply:

Similar Artilces:

countif Access 2000
I,m trying to count how many numbers, in a column, that have a value greater than zero. Any ideas anyone In query design view, type something like this into a fresh column in the Field row: [MyField] > 0 Depress the Total button on the toolbar. Choose Count under this field. If you wanted to do it in code: DCount("*", "MyTable", "MyField > 0") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Zab" <Zab@discu...

How can I begin numbering pages(Pub) on first page after inside
I want to begin automatic page numbering on the actual third page. In other words, the cover and inside cover as well as the back cover and inside back cover will not be numbered. Can this be done? What version Publisher? Go to page 3, Insert, Section, Check Begin a section with this page. Start at page 1. Insert page numbers, if you are viewing a single page spread you will have to apply the page numbers on both sides. Create a new master page. Apply to the last page and cover. -- Mary Sauer http://msauer.mvps.org/ "bamatoni" <bamatoni@discussions.microsoft....

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Create new records from existing ones based on 'rule'?
I am trying to pull apart records I uploaded that are set up something like this; MainField | Other Fields | AssociatedFIeld1 | AssociatedFieldN There can be anywhere from zero to 6 Associated Fields. Trying to break a new record apart for each e.g. MainRecordX | Other Fields | WidgetA | WidgetB MainRecordY | Other FIelds |WidgetC | Widget D | WidgetE MainRecordZ | Other FIelds should become MainRecordX | Other Fields | WidgetA MainRecordX | Other Fields | WidgetB MainRecordY | Other Fields | WidgetC MainRecordY | Other Fields | WidgetD MainRecordY | Other Fields | Wid...

How can i use the dash character, "-", to sort data?
If i wanted to sort the info below into two different columns, how would I do it?SOMRVLLE - SOMERVILLE SOUTH - SOUTHERN REG. ST. JOHN - ST. JOHN VIANNEY ST. ROSE - ST. ROSE HIGH ST. ROSE - ST. ROSE HIGH STAF - SOUTHERN REG. Hi use 'Data - Text to columns' to split the content into two columns -- Regards Frank Kabel Frankfurt, Germany "Val" <Val@discussions.microsoft.com> schrieb im Newsbeitrag news:1F8EE451-7B54-49D1-B8C7-B21575180B81@microsoft.com... > If i wanted to sort the info below into two different columns, how would I do > it?SOMRVLLE - SOMERVILLE ...

consequent numbers
How can I create consequent numbers in a spread sheet? I would like to create a personalised invoice template for my business. Everytime I open the file it should change the "invoce number" N+1, like in a counter. Thanks for your help, harry Name a cell InvNum by selecting any cell and then typing in InvNum in the space to the left of the formula bar or use insert>name>define Then put this in the ThisWorkwook module by right click on the excel logo just to the left of FILE and inserting this. Private Sub Workbook_Open() [InvNum] = [InvNum] + 1 End Sub Then, you get wha...

Autofilter Wildcards Not Working on Number Column
Hello everyone...n00b question. I'm using Excel 2003 to split up a daily spreadsheet between me and 2 of my peers. We split the orders based on the last 2 characters of the order. The orders are all 7 digits and all numerical. Exp... 3215453. For example... my number range is 00-33. Every morning I have to scroll through the spreadsheet and manually select my orders to obtain my daily workload, which can take up to 40 minutes. I tried using auto-filter with wildcards * & ?, but they don't work. I'm typing my custom filter values as follows ?????33 and *33, but they don't ...

Finding if UserID/PW Exists in XML File Using XPath?
I have PW/UserID info as follows and would like to use xpath to find if a userid and pw exists within the xml file. What Xpath query statement do I need and what query command do I need? <users> <user> <userid>joe</userid> <pw>secret</pw> </user> <user> <userid>sue</userid> <pw>hello</pw> </user> </users> Thanks for your help BTW, where Can I find a good resource on using XPath Earl ...

Excel page numbering in the header
In a workbook of nine worksheets, I was able to get numbering like 1 of 9, 2 of 9, etc. as advised by Ragdyer. But there are two problems: 1. The numbering in the center of the header was got by using "page 1 of ?". But the size of the fonts on the different sheets are different and I can find no control to be able to adjust these both for size and bold. 2. If I seek to do the same in the spcial header and special footer, my only choice is to use two icons next to each other--one is like a # sign, and the other two signs. No matter how I try, I cannot get these two to give pag...

Filter for 225 or 233 numbers #3
Hi again, just like to say thanks for all your help, really appreciated. I think I may have got it working by putting a 5 in the code ie =OR(MID(A1,5,3)="233",MID(A1,5,3)="225") instead of ==OR(MID(A1,4,3)="233",MID(A1,4,3)="225 and the same for your one frank but does this mean that it counts fro the left ? sometimes the no.s come in like this 92254898 or 792335698 ? -- hogan ----------------------------------------------------------------------- hoganc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1498 View this ...

Random Number Non Repeating ...
I have the numbers 1 - 55 that I would like to display in a column randomly without repeating. The numbers should be whole numbers only. Can you help me? I tried following some of the other posts, but I couldn't get it. Thanks. Enter the numbers 1-55 in a column, and in an adjacent collumn enter =RAND() and fill down. Now sort both columns by the RAND column and you'll have the 1-55 shuffled. Bob Umlas Excel MVP "a_ryan1972" <a_ryan1972@discussions.microsoft.com> wrote in message news:DBC6E0F7-E136-4CEC-991E-25C5302732DD@microsoft.com... > I have the numbers ...

Numbering, tab doesn't work
I have some numbered bullets and I use to be able to tab to ident to the next level or shift-tab to back out a level, but now it doesn't work. Is there a switch somewhere to turn this back on? -- wstu In the AutoCorrect dialog box, click the AutoFormat As You Type tab. Make sure that "Set left and first indent with tabs and backspaces" is selected. To display the dialog box: - Click Tools | AutoCorrect Options (Word 2003). - Click the Office button, and then click Word Options; in the Proofing category, click AutoCorrect Options (Word 2007). -- ...

where do I find my 25 product key number on a pre installed PC
I bought my acer computer from tesco, it was pre installed with microsoft, I did not get a CD with it and have looked everywhere on the packaging but I cannot find the 25 figure product key number. Look on the computer itself, it's often there. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "stainforth" <stainforth@discussions.microsoft.com> wrote in message news:59673B2E-E271-4C04-A792-FAC52E039FD1@mic...

How can my formula handle inserted rows?
I am using (more likely misusing) COUNTA to validate that all cells within the specified range are populated, but I have a question regarding how to handle inserted rows. Here is my current formula: =IF(COUNTA(A1:A10, B1:B10)=20,"All cells within specified range are populated") However, if someone inserts a row between the 1 and 10, the contents in row 10 get shoved to row 11, and I would want to expand my formula range dynamically to accomodate this insert. How would I accomplish this? Thanks for any insight! If that range were simpler--just a single area, the...

Adding a -L to the end of a list of numbers in a column.
An example: 2.1.21-L 2.1.22-L 2.1.23-L 2.1.24-L 2.1.25-L I would like to add -L to the end of each number in the column without doing the manual process. How can I do this? 2 2.1 2.1.1 2.1.2 2.1.3 2.1.4 2.1.5 2.1.6 2.1.7 2.1.8 2.1.9 2.1.10 2.1.11 2.1.12 2.1.13 2.1.14 2.1.15 2.1.16 2.1.17 2.1.18 2.1.19 2.1.20 2.1.21 2.1.22 2.1.23 2.1.24 2.1.25 2.1.26 2.1.27 2.1.28 2.1.29 2.1.30 2.1.31 2.1.32 2.1.33 2.1.34 2.1.35 2.1.36 2.1.37 2.1.38 2.1.39 2.1.40 2.1.41 2.1.42 2.1.43 2.1.44 2.1.45 2.1.46 2.1.47 2.1.48 2.1.49 2.1.50 2.1.51 2.1.52 2...

How to count existing records where StudentID = ThisStudent
I'm trying to count the number of classes a student has in their "Student Schedule" table as we enter more records for that student. Assuming the records are entered in the order of the student's preference, then we can set the "Preference" field to be equal to the number of records. But I'm new to Access and it doesn't behave as I expect. I get runtime error '2001', "You cancelled the previous operation". Perhaps I need some kind of post or 'save record' command? I've attached the following code to the event called A...

inserting PDF into publisher?
Hi, I'm putting together a magazine in Publisher and have been sent some ads in PDF format. When I use the snapshot tool the quality seems to deteriorate - is there any other way of getting the files in without losing quality? Thanks Sarah Sarahs999 <Sarahs999@discussions.microsoft.com> was very recently heard to utter: > Hi, I'm putting together a magazine in Publisher and have been sent > some ads in PDF format. When I use the snapshot tool the quality > seems to deteriorate - is there any other way of getting the files in > without losing quality? Use GhostScr...

number list compare
How do a compare a number to a list of numbers to get the next highest and next lowest number? In article <6DBB1D07-4ABE-4885-8DAE-5552AC7603C6@microsoft.com>, "jpmmschi" <jpmmschi@discussions.microsoft.com> wrote: > How do a compare a number to a list of numbers to get the next highest and > next lowest number? Assuming that your list of numbers are in Column A... Next lowest number: =MAX(IF(A1:A10<B1,A1:A10)) Next highest number: =MIN(IF(A1:A10>B1,A1:A10)) ....where B1 contains the number being compared to. Both these formulas need to be entered...

making a cell fixed number to a input number
I am doing a caclculation. Some of the time I want to do it with a percentage and sometimes with a dollar amount. If I punch in a dollar amount I want it to come up with the persentage. If I type in the percentage I want it to come up with the dollar amount. How can I make the formula stay in a cell even if I punch something in. fwday12@msn.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ And how do you tell .20 (%) from .20 (cents) "fwday" <fwd...

Advancing A Number With The Push A Button
Greeting, I have an invoice that uses a button to advance the invoice number by 1. When I wrote it the numbers were just numbers, but since then a letter has been added to the front of the number (was 17445 now D-017445). My advancing code no longer works and I don't know why! Here is the code: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Range("InvNoLast").Value + 1 End Sub Anyone have an idea on how to fix it? Any help would be most appreciated. TIA -Minitman Hi one idea: why not format the cell with the custom format: "D-"00000...

Phone Numbers Repeated
I have a chart in excel that looks like this Ladd Design Koch Stephanie Ms. 156 Farm Rd. New Canaan, CT 06840 2039663032 F2039660170 except in excel each piece of data is a different cell so in total i have 9 cells Co. Name, Last Name, First Name, Title, Address, Town & State, Zip Code, Phone Number and Fax number. There are about 1700 Rows in total. And there are many repeats as a lot of the data was copied and pasted from other sources. My problem is these repeats need to be removed. The repeats are in the phone numbers, if there is a repeated phone number the entire ...

Numbers as Text
I am copying a column of numbers, formatted as Number w/ zero decimals, to a column formatted as Text. I am using Paste Special - Values only. The numbers are all four digits, such as 1101, 1105, 1111, 1123, etc. After the Paste, the numbers show up 'rounded' in the new (Text) column, with the last number always a 0. 1101 becomes 1100. 1105 becomes 1110. 1111 becomes 1110. 1123 becomes 1120. Any ideas why? Hi why do you want to put this numbers in a column formated as 'Text'? -- Regards Frank Kabel Frankfurt, Germany Bill wrote: > I am copying a column of numbers...

LOOKUP function does not work with row vector of numbers
Here is a spread sheet on Excel 2003: A B 1 =1.11+2 2 2 =LOOKUP(3.11,A1:B1) The B2 cell is #N/A, although A1 is evaluated to be 3.11 It seems if you match against 1.1+2, it works. Could anyone tell me where things is going wrong? Thanks What are you trying to do? Do you want to return the *last* (rightmost) numeric value in the range? If so, try this: =LOOKUP(1E100,A1:B1) -- Biff Microsoft Excel MVP "JRaSH" <jrash06@163.com> wrote in message news:O50aaD2iKHA.1824@TK2MSFTNGP04.phx.gbl... > Here is a...

print batch number on receipts
is there a way to add the batch number to a receipt? FYI Batch.Number Returns the number of the batch currently being processed. "Sammy" wrote: > is there a way to add the batch number to a receipt? ...

How to create a formula to remove multiple . in a numbers?
Ex: 11.2101.1030.0000.0000.1982 how can I remove these dots in multiple numbers on a spreadsheet? If you really mean a formula, meaning you will retain the dotted values and display the number without the dots in another cell, then use this formula... =SUBSTITUTE(A1,".","") If, on the other hand, you are trying to modify each cell's value in place, then you can use Edit/Replace from Excel's menu bar... put a dot in the "Find what" field and leave the "Replace with" field blank (empty). -- Rick (MVP - Excel) "Che...