Formatting of numbers in excel

Try this, with your numbers in column A ,put this in B1 and copy down

=MID(A1,FIND(" ",A1,1)+1,10)

Sorry, try this instead

=SUBSTITUTE(MID(A1,FIND(" ",A1,1)+1,10),"-","")

...perhaps a combination of the 2 functions suggested....


=SUBSTITUTE(MID(A1,FIND(")",A1)+2,LEN(A1)),"-","")

Slighty different to your original number - an extra space added, so try this

=SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(")",A1,1)+2,20),"-","")," ","")

Hi Hazel - This idea may also help with the different formats.

1. Make a backup of your spreadsheet (just in case things go wrong)

2. You might want to copy all phone #'s into a new column (so you can compare before and after)

3. Now on the new column, highlight the entire column (click on column G at top for example, so all phone #s are selected)

4. Now you use the menu EDIT >>> REPLACE function

5. To eliminate all leading parenthesis, key for example:
FIND WHAT: (
REPLACE WITH: [don't key anything in here]
Now select REPLACE ALL button

6. Now do the same thing for the trailing parenthesis, key for example (keeping the column highlighted as noted in step 3 above):
FIND WHAT: (
REPLACE WITH: [don't key anything in here]
Now select REPLACE ALL button

7. Now do the same thing for the dashes, key for example:
FIND WHAT: -
REPLACE WITH: [don't key anything in here]
Now select REPLACE ALL button

8. Now do the same thing for any spaces in the phone field, key for example:
FIND WHAT: [hit the space bar once]
REPLACE WITH: [don't key anything in here]
Now select REPLACE ALL button

Hope this works for you, as I've used this technique to clean up data often, esp. if the patterns or quality of info varies

If what NBVC and OC are sharing works, please use their approach if you're making progress. There are so many different ways to do things in Excel. I don't want you to get sidetracked as we all posted close to the same time