#### ::: Telephone Number Comparison or Data Integrity

```Hi,     I have an Excel file full of telephone numbers that are like
this: +001 (812) 123 123 123     Into a new tab I will change them to:
+001812123123123     Into a 3rd tab I would like to ensure that I only
deleted the spaces and the parenthesis (data integrity).     I am
looking for a formula that adds each number within the cell and
compare these 2 totals.     In other words:

Tab1
+001 (812) 123 123 123
0+0+1+8+1+2+1+2+3+1+2+3+1+2+3=30

Tab2
+001812123123123
0+0+1+8+1+2+1+2+3+1+2+3+1+2+3=30

Tab3
30 compare to 30 = ok      if not     = bad

Would anyone know how to do this?     Or have a better suggestion?
Many thanks for your help.     Jacques

```
 0
7/27/2007 1:26:11 PM
excel 39879 articles. 2 followers.

2 Replies
634 Views

Similar Articles

[PageSpeed] 50

```First you should delete spaces and parens.

Format the cell to show them................000 up to 15

Then use this formula in a helper cell to get the sum of digits.

=SUMPRODUCT(--MID(\$A\$1,ROW(INDIRECT("1:" & LEN(\$A\$1))),1))

Gord Dibben  MS Excel MVP

On Fri, 27 Jul 2007 06:26:11 -0700, infojacques@gmail.com wrote:

>Hi,     I have an Excel file full of telephone numbers that are like
>this: +001 (812) 123 123 123     Into a new tab I will change them to:
>+001812123123123     Into a 3rd tab I would like to ensure that I only
>deleted the spaces and the parenthesis (data integrity).     I am
>looking for a formula that adds each number within the cell and
>compare these 2 totals.     In other words:
>
>Tab1
>+001 (812) 123 123 123
>0+0+1+8+1+2+1+2+3+1+2+3+1+2+3=30
>
>Tab2
>+001812123123123
>0+0+1+8+1+2+1+2+3+1+2+3+1+2+3=30
>
>Tab3
>30 compare to 30 = ok      if not     = bad
>
>Would anyone know how to do this?     Or have a better suggestion?
>Many thanks for your help.     Jacques

```
 0
Gord
7/27/2007 2:37:04 PM
```Hello Gord,     Thank you for your solution.     I will try to apply
it and understand it.     But this will be tomorrow.     I wish you an
excellent week-end.     Jacques

```
 0
7/27/2007 3:09:56 PM

Similar Artilces:

how do I add numbers in list of columns
entered numbers in columns of excel and want to total them going horizontal and vertical to get grand total for each column. Can you explain how to do this? For e.g. You enter 1-10 in A1-A10 You enter 11-20 in B1-B10 So, in A11, you type SUM(A1:A10) to get Column Grand total and copy it to B11 to get Grand column total for B And, you type SUM(A1:B1) in C1, to get Row Grand Total and copy it to C2 to C10 to get all Row Grand total Hope this helps. "Amadauss" wrote: > entered numbers in columns of excel and want to total them going horizontal > and vertical to get gr...

sorting data in columns
How can I sort multiple columns simultaneously? i.e. I have sixteen columns of numerical data that I want to be able to apply the same Sort to each column individually but I do not want to have to highlight each column separately as this will take considerably longer. Save your workbook first! Try sorting one column, then selecting the next and hit the F4 key. It shouldn't take too long if it works ok. Spiderman wrote: > > How can I sort multiple columns simultaneously? i.e. I have sixteen columns > of numerical data that I want to be able to apply the same Sort to each &...

What to do?
Hi Everybody? I have problem when I try to build offline data file from Pivot tabel which is connected to Cube on the Analysis Server. Excel return this message: No columns that microsoft excel can use were returned from this query! What to do? ...

Compare Data
Let's say I want to compare data thusly: I have two columns of numbers, G and N. Column G has more numbers than column N, and includes every number in column N. In each cell in column O is a string associated with the number in column N. I want to comare a number in column N with all the numbers in column G and when I find a match, copy the string in column O to the appropriate cell in column M. I want to repeat this process until all the strings in column O have been matched to their numbers in column G. Whew! So. How do I do this? Thanks, Tommy One way: M1: =VLOOKU...

DATA DUMP WARNING #2
Maybe the ultimate lesson in this is backup backup backup. Do backups early and often. and use a large cycle - don't write a backup onto the immediately previous media! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I do not respond to any emails that I have not specifically asked for. <DrWoodardOnDS@hotmail.com> wrote in message news:11095315...

Sorting numbered system that relies on decimals
eg. This is the result I am looking for: TS-501.9.8 TS-501.9.9 TS-501.9.10 TS-501.9.11 TS-501.10.1 TS-501.10.2 TS-501.11.1 This is the result I am getting: TS-501.10.1 TS-501.10.2 TS-501.11.1 TS-501.9.10 TS-501.9.11 TS-501.9.8 TS-501.9.9 As you can see the numbers between the dots ascend from 1, however excel takes them as decimals (eg 0.1 = 0.10 = 0.100). Suggestions appreciated! --- Message posted from http://www.ExcelForum.com/ Hi It's possible to do using helping column, and a huge formula in it. But why not to convert your column into form TS-501.09.08 etc. Then you haven'...

How would you go about pulling data from Access database into Excel? (Like weekly reports) Troy, Data|Get External Data|New Database Query Select MS Access Database The Wizard should take you through the remaining steps of selecting the data you want to import and the criteria (if any) that you wish to use. PC "Troy" <corptkm@yahoo.com> wrote in message news:nLDXa.3726\$Jk5.3138269@feed2.centurytel.net... > How would you go about pulling data from Access database into Excel? (Like > weekly reports) > > You can also push-it-out of Access to Excel by (While...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...

Pivot Table data source
I have created a pivot table in the same workbook as the data source but whenever I try to refresh the data I get a message saying "This command requires at least two rows of source data.............." On checking the data source some cells on only one row are selected. If I change the selection to what it should be everything works fine but the selection reverts back to the same row and cells the next time I open the workbook. How can I make Excel (2007) retain the data source selection? Hi Pete If you have given the correct range, then it should retain the values. Howe...

Sorting page numbers
Is there a simple way of sorting pages back into numeric order after building a large spread sheet so they appear in ascending order on the monitor. Without renumbering that is Thanks Mike Hi Mike, Your question is not clear to me, so here are three interpretations even if it is a stretch compared to even what you indicated. If you sorted the rows into a different order and want to restore them you can restore the order if you previous numbered your rows by placing a 1 on a new column (helper column) and used the fill-handle to place 2 into next cell, and 3 into next, etc. If you mean...

Email template insert data fields into subject line.
Hello, Is there a way to insert data fields into the subject line in email templates? I would like to add a case number so our customers can see it in the Subject line. Thanks, Steve ...

Range of data is variable...
I am working on a year-end report for my clients that shows them information associated with their accounts. Most of the information on each client's reports relates to the current year and is easy to retreive from the tables that store all the clients' information. However, there is historical information for each client that is more difficult to report. I want to display a graph for each client showing the year-to-year change in "value" for them. But since each client has a different number of data points, it is proving hard for me to format the graphs properly. ...

data labels disappear
Hello all, I'm using 2003, I have a scatter chart and whenever I check the box to show data labels, then save (to a share point workspace) then close & re-open, the data labels disappear? Thanks for your assistance! Vera ...

Conditional format based on data from another column
Hi Yesterday I asked about the possibility of conditional formatting a range of 20 random numbers into 2 different formats. Thank you for that, but now it seems that 3 of my 20 numbers are now requiring a different cell to be highlighted in yet another colour. Currently the situation is as follows:- Column G has a list of random numbers ranging from 1-20, with columns either side pertaining to the random number located in the relevant cell in Column G. It now appears that 3 numbers in this random list do not require highlighting in Column G and have amended the condition formattin...

How can I delete a space/character from a number of fields?
I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. Why not just select the all the cells and use Excel's Edit/Replace to replace the hyphens with the empty string (put a hyphen in the "Find what" field and leave the "Replace with" field empty)? -- Rick (MVP - Excel) "BABs" &...

Rename Data file
I am using outlook 2003 and I have to data file for two email accounts, the problem is the two file have the same name( presonal folders). I want to rename them. Was there some reason for not simply creating subfolders for each email account and using rules to file appropriately? "hazzaa" <anonymous@discussions.microsoft.com> wrote in message news:206da01c45933\$9a83a760\$a501280a@phx.gbl... > I am using outlook 2003 and I have to data file for two > email accounts, the problem is the two file have the same > name( presonal folders). > > I want to rename them....

Unique data in column or row in Excel
I have a problem with posibility of repeating data in column. Of course this is normal behaviour of Excel, but I need to assure unique data in column. Is there a way to do this without an extra effort? Hi Ali, Try to use this formula in Data/Validation/Custom: =ISERROR(MATCH(A1,INDIRECT("A1:A"&CELL("row",A1)-1),0)) Regards, Stefi „Ali” ezt írta: > I have a problem with posibility of repeating data in column. Of course this > is normal behaviour of Excel, but I need to assure unique data in column. Is > there a way to do this without an extra effo...

Squishing Data Down.
I have recently received some coporate documents made in Excel format that have a very nice "1-2-3" tool bar on the left side of the screen at the top. Below each of them are a plus sign. When you click on the plus sign, more data appears in a designated row; and then a little minus sign appears when you want to close it, click it. Makes huge spreadsheets more manageable. I want to know how to make that little section appear for a spreadsheet I am working on. Thanks. Take a look at "About using outlines" in XL Help In article <BB09E5F0-6BF1-451F-B7FD-4D0501D...

Integration 03-28-06
Do we know yet when crm 3 integration to GP (great plains) will release? It's great that MS keeps their customers informed :-( -- Brandon Presentations Direct - "Document Finishing Solutions" http://www.presentationsdirect.com I'm assuming it was mentioned at convergence - any attendees care to comment? ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Brandon" <bsmith@presentationsdirect.nospam.com> wrote in message news:u2OSUurUGHA.5108@TK2MSFTNGP09.phx.gbl... > Do we know yet when crm 3 inte...

Extract data in the right part of a cell
I have some cells contains "*" , I want to extrat the text to the right of the "*". For example, if A2 contains "txt*123", I want it turn out to be "123". Thanks. Try the following formula, =MID(A1,FIND("*",A1)+1,LEN(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "plumstone" <plumstone@discussions.microsoft.com> wrote in message news:731CB03C-AD8F-43D8-BFAB-6AD50DEBE799@microsoft.com... > I have some cells contains "*" , I want to extrat the text to th...

Problem importing data to Money 2006
I'm importing data from Money 2000 into 2006 and the dates and quantities of investment transactions are not transferred. I exported multiple accounts within a file with File/Export and changed the file names to reflect the account names (with .qif after them) to a removable flash drive. Followed import instructions (File/Import), set up multiple accounts and moved them as a group. The transfer appeared to be successful except date and quantities were not present. Any ideas? -- leahn In microsoft.public.money, leahn wrote: >I'm importing data from Money 2000 into 2...

Valuable Microsoft User Data folder cannot be risked
In response to a question on what other Office preferences to remove to return to default preferences, a respondent to this newsgroup on Sept. 18 advised: >You missed the biggy at ~/Documents/Microsoft User Data. >Dump that one and you should be good to go. Of course, most of us know that would be an extremely costly mistake. But there may be some who less familiar with Office X. To those individuals who read such "advice," please know that removal of Microsoft User Data would sacrifice your entire Office identities database--all your contacts, notes, tasks, mail, calend...

Cannot set the LOCALE IDENTIFIER of the data source #2
In order to use the "translations" feature of Sql Server Analysis Services 2008 we need to set the locale identifier of the OLEDB connection string. However, it doesn't seem to be possible in Excel 2007. Here are the steps to reproduce the problem. 1- Open Excel 2007 2- Data > From other sources > From Analysis Services 3- Select a server and a database (it doesn't matter which), click next, click finish 4- Click properties, clear the "retrieve data and errors in the Office display language" checkbox, go to the definition tab, add "Locale Identifier=105...

Transfer data in an other text box field on a condition
Hi folks, I have a field on my report that show me a sum of "cartons to received" for each truck appointed. now I want to transfer that sum of "cartons to received" into another text box if the cartons are received. Anyone have an idea, how can I do this? If you need additional details, let me know. Thanking you in advance for your support Fred's ...

auto assign lot number /serial number in inventory transaction
auto assign lot number /serial number in inventory transaction like in Sales order processing ---------------- 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 the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=63d06705-21f8-45b3-a2e9-a64c2b...