I havea few thousand 10 digit phone numbers all in one column. I need to separate these into two columns with the area code in one column and the remaining 7 digit phone number in the other column. How do I do this? What is the formula?? Can anyone help me?! THANKS and HAPPY HOLIDAYS!

0 |

12/19/2005 8:02:49 PM

Select the column. Then Data>Text to Columns>Fixed Width. Select the three digits on the left and Finish. Make sure you have an empty column to the right of the data before splitting. Gord Dibben Excel MVP On Mon, 19 Dec 2005 20:02:49 GMT, "billygalkowski" <u16801@uwe> wrote: >I havea few thousand 10 digit phone numbers all in one column. I need to >separate these into two columns with the area code in one column and the >remaining 7 digit phone number in the other column. > >How do I do this? What is the formula?? Can anyone help me?! THANKS and >HAPPY HOLIDAYS!

0 |

12/19/2005 8:17:01 PM

=IF(LEN(RC[-8])<4,CONCATENATE(VLOOKUP(RC5,accounts,2),TEXT(RC[-8],""00"")),RC[-8]) =IF(ISERR(RC[-2]),0,IF(ISNA(RC[-2]),0,IF(EXACT(RC[-6],RC[-1]),1, Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com The first checks the cell 8 columns to the left. If the length of the text in that cell is greater than or equal to 4, the formula returns that cell's value. If not, the formula returns a concatenation of a lookup in the accounts table based...

give me complete formula See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Naveed" <Naveed@discussions.microsoft.com> wrote in message news:29B7C0D6-1DED-4321-B1EB-CA6DDCE73852@microsoft.com... > give me complete formula > ...

Hi there, my problem is, that i need something like: there is a function in a excel-cell (for example '=setvalue("sqlserver", "DB", "table")'). now i type in a value (for example 100) then the function "setvalue" must write this value (100) in the server, db and table from the parameters. after leaving the cell the formula is still the same and only the value 100 ist visible for the user. and if i go back to the cell than i can see the function in the menubar. there is a product from applix (TM1) and they did it. the problem is, that the souce...

Hello, I am finding it challenging to trace my formulas when they link to other spreadsheets. It is a manual and tedious process, and I am thinking that there has got to be a faster way than physically searching for the cells to which the formulas are referring. Does anyone know of a fast way to jump to precedents and dependents of formulas. (I did notice the formula auditing toolbar, but it seems to only work when you are trying to trace formulas in the SAME worksheet). Thanks for any suggestions. For tracing across sheets, if you were to double-click on the dotted arrow that points to/f...

Is it possible to use a formula or the content of another cell to set the number of decimal places? For example, if cell A1 had a formula which results in the value 4, I want cell C6 to show 0.0000 and if A1's value was 7 I want C6 to show 0.0000000. Thanks.... Hi not possible with a format. You'll have to use VBA -- Regards Frank Kabel Frankfurt, Germany "mike hutchins" <ma_hutchins@hotmail.com> schrieb im Newsbeitrag news:ccdd7764.0409240642.3e6bb7d0@posting.google.com... > Is it possible to use a formula or the content of another cell to set > the number of ...

Hi I have a workbook that has several formulas and 15 sheets which causes the workbook to be very large. Is there a way to reference to a formula instead of copy and pasting the same formula on every worksheet? thanks On Feb 17, 6:23=A0pm, "Fawn Lagimodiere" <flagimodi...@shaw.ca> wrote: > Hi I have a workbook that has several formulas and 15 sheets which causes > the workbook to be very large. =A0Is there a way to reference to a formul= a > instead of copy and pasting the same formula on every worksheet? > > thanks Group the sheets, select the first she...

I have a spread sheet with a large number of categories and a corresponding %. I want to concantenant the heading and the attached % in an additional column. So far so good but when I concantenant the % any value other than 0% reverts to the full number below. So 5% displays as .05687899797897 (or what ever number sits underneath) Is there a way of getting it to display as a percentage. Ultimately I want to import it to Visio (so the same problem may occur!). Any help appreciated Hager as an example...assuming a1 has your percentage and a2 has your heading, would somet...

I have a spreadsheet; 25 participants for 31 days each participant must log a catagory (eg PC) the spread sheet count the number of PCs for the 31 days (eg. formula (=COUNTIF(E10:AI34,"PC")). Question how can I calculate how many participates have logged a catagory PC within the 31 days. Hi C, One way, Put =COUNTIF(E10:AI10,"PC") in cell AJ10 And drag down to AJ34 Then in another cell of your choice put =COUNTIF(AJ10:AJ34,">0") HTH Martin "C Sealy" <C Sealy@discussions.microsoft.com> wrote in message news:13350DAB-8BEC-4731-B235-B4B...

I have a table that I have created a report from. The calculations are done on the report itself. For example field c was blank on the table so I created a formula in there a/b, that worked. Now I am trying to get the avg of c. It won't work. Scott B wrote: >I have a table that I have created a report from. The calculations are done >on the report itself. For example field c was blank on the table so I created >a formula in there a/b, that worked. Now I am trying to get the avg of c. It >won't work. When you want to aggregate a value across multple records, you ne...

I have a really large spreadsheet with loads of formulas (lookups and vlookups plus mathematical functions) But it takes forever to recalculate cells whenever I cut and paste or change the contents of any cell within the workbook... Are there any formulas that are particularly slow and if so, are there better ways to perform the same task? Thanks You could try turning off automatic calculation Tools/Options/Calculation Set Calculate option to "Manual" or "Automatic except Tables" and then press F9 to force calculations when needed. I hate using this option , but sometime...

Dear Leo Heuser, I have enter your formula =(A1>0)*(MOD(A1,10)=0) but why is it that when I enter 10 or 20 in the cell, excel disallowed me? I need only to restraint user from entering odd numbers in the cell, that means allow even numbers but these even numbers must be in multiples of 5? Thank you. Ringo Tan "ringo tan" <ringotan@discussions.microsoft.com> skrev i en meddelelse news:B435BEF7-6CA9-455E-ABBD-FC73DD8479F2@microsoft.com... > Dear Leo Heuser, > > I have enter your formula =(A1>0)*(MOD(A1,10)=0) but why is it that when I > enter 10 or 20 in ...

Is there an easy way to build a formula that will only take values into consideration when the auto format is used. So, if I have a table of data and a formula based on this table and I filter one of my data columns to some vause then I would like my formula to automatically update. Is this possible? Thank you, CH The SUBTOTAL function ignores rows that are hidden by an autofilter (and optionally, also rows that were hidden manually). It can perform a wide range of operations, including sums, averages, counts, standard deviations, products, maximums, minimums, etc. Check E...

I'm working with payroll data. I have a formula that displays payroll data that's based on a payroll date. Until the payroll date is reached the cell reads FALSE. Is there a way to display a 0 instead? This is the formula =IF(A30>=A36,SUM('3-15'!X38 +'3-15'!AB38 + '3-15'!AF38 + '3-15'!AJ38 + '3-15'!BT38)). Cell A30 contains the current date. I have another formula that adds the number of hours worked in the 2 pay periods in the month. If the hours add up to <100 a message appears. The problem is it's using the same cells to calul...

I have a long formula that I want to be able to use on any worksheet (current, previously saved, or new). How can I save or store it so it is always available (like the pull down lists in Functions). I know this is probably a very basic operation, but I'm not so swift on anything beyond the basics of EXCEL. Joe B ...

I have created an Excel worksheet which I update with new values each day (golf scores). I keep the last 10 scores but want an average of the last 5 scores. How can I move the data one column to the left and not change the averaging formula so that when I post the new score it still uses the original formula (sum of G5 to K5 devided by 5). If you put a "$" before any cell reference, it will "freeze" the formula value. So, if your formula references cell G5, you can use $G5 to freeze the column or G$5 to freeze the row or $G$5 to freeze the entire cell reference. ...

I have a worksheet we use as a printout, part of that sheet has a formula(s) we use for ease of use to update the sheets weekly informtion. Just before we do the print out we resort the data based on one column of information. The problem I have is this, I have two columns of information using formulas that won't sort along with the rest of the information. this formula: =INDIRECT("Scorecard!X"&2*(ROW()-3)+2) and this formula: =INDIRECT("Scorecard!X"&2*(ROW()-2)+1), are keeping my data from sorting correctly. My column headings are this; Team, Name#1, Ph#,...

I would like to use a command such as vlookuo to lookup a value and insert that value into a path that will identify a referenced spreadsheet. Can that be done? i.e. find a string in a cell and use that string as part of a file name. Chuck Yes. Within the call of the file name put something like: & Range("A1").Value & HTH Otto "Chuck" <chuck@newmanArchitecture.com> wrote in message news:u%23HYWrVsDHA.2340@TK2MSFTNGP12.phx.gbl... > I would like to use a command such as vlookuo to lookup a value and insert > that value into a path that will ide...

I have a formula that reads =SUMIF(B11:B467,B479,D11:D467) I want B11:B467 and D11:D467 to remain the same in the formula, but want B479 to become B480, B481, B482, etc. as I move down the cells i the column. I cannot figure out how to do this. If I write th formula in one cell, then drag it down, all the numbers change... Thanks in advance for any help anyone can give me... Dre -- Message posted from http://www.ExcelForum.com Hi You need to use absolute references: =SUMIF($B$11:$B$467,B479,$D$11:$D$467) -- Andy. "Drew >" <<Drew.16v8g5@excelforum-nospam.com...

Hi All, I know this going to be easy for you guys but I'm stuck on it. How do I write this formulae. If the total number of places available for each class is zero, then the result None should be returned, otherwise the actual total should be displayed. The cells are F20:G20. F20=69 G20=22. the answer needs to be replicated neither cell is absolute. Thanks, kfh. Firstly ensure the cell you write the formula into is set to type general. add formula =if(test formula,answer if test is true,answer if test is false). eg =IF(SUM(F20:G20)=0,"None",SUM(F20:G20)). If the cell ty...

Hi. Is it possible to concantenate one cell with text with another cell with a number in it and take that result and then use it in an index/match formula to find a result? Or will it not work? It looks like this: C1 D1 E1 2 3 A C3 D3 E3 F3 A 2 3 1 H H H 2 S S S A3 D D D Now, Concantenate D1 & E1, then use the Index Match with cell C1 to find a result of D in cells D4 through F7. Hope I explained my question properly. Thanks. ...

Hi, I have several cells CW2, CZ2, AB2 etc I would like to test if any of these cells values are True, I thought that I could use OR(CW2 = True, CZ2 = True, AB2=True) But these did not work, an help is appreciated. That expression worked ok for me. How did it not work for you? And are you sure you have the boolean value TRUE in the cell? Could you have a plain old string "True" in the cell instead? MS Forum Newsgroup User wrote: > > Hi, > > I have several cells CW2, CZ2, AB2 etc I would like to test if any of these > cells valu...

What is the formula for finding the difference between the dollar amount of two cells when sometimes the result will be positive and other times negative? Hi try ABS(A1-B1) HTH Frank asussertown wrote: > What is the formula for finding the difference between the dollar > amount of two cells when sometimes the result will be positive and > other times negative? Thank you, I will try this. "Frank Kabel" <frank.kabel@freenet.de> wrote in message news:OmgIgKc5DHA.2580@TK2MSFTNGP11.phx.gbl... > Hi > try > ABS(A1-B1) > > HTH > Frank > > asusse...

I have multiple worksheets and want to be able to type in a district name in Cell C1-sheet 1 and have it pull the corresponding ISD infomration for payroll, Premium, and fixed cost for years 03-04 from sheet 2 into sheet1 for cells B5 for 03-04 payroll, C5 for 03-04 premiums, and D5 for 03-04 for fixed cost. Also need the formula to do the same thing for 04-05 in sheet 1 from sheet 3 Sheet 1 Cell A B C D 1 2 Payroll Normal Premium Fixed Cost 3 4 2003-04 5 2004-05 Sheet 2 Cell A ...

Dear Experts, Please find the following formula =LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1). how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array Result? because if i do "EVALUATE FORMULA" it will result either 0 or 1. Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the records of AC.DATE. thank you for your kind help and guidance. TIA. respectfully, andri On Wed, 10 Feb 2010 05:59:01 -0800, Andri <Andri@discussions.microsoft.com> wrote: >Dear Experts, > >Ple...

Often, I lose track of brackets in long formulas. If I could expand the view in the formula bar it may make it easier to create and edit long formulas: C7+C28 C26*C12 C30/( )+( )^3 =SQRT( ) Is there an automatic way to show the formula bar like this? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in t...