MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Linking and unlinking data in a single field


Posted by H on November 15, 2001 8:33 AM

I have a problem where I have two spreadsheets different files. within them I have one field and they are phone numbers. Spreadsheet 1 has the phone number in the following format

024 76222222

Spreadsheet two has it in the following format

02476222222

How can I get either spreadsheet 1 to look like spreadsheet 2 or vice a verse (so basically I want to either add or take away a space between the area code and number)? Is there a simple way of doing this.

Example 1

spreadsheet 1 = 024 76222222

spreadsheet 2 = 024 76222222

Example 2

spreadsheet 1 = 02476222222

spreadsheet 2 = 02476222222

The purpose of this is so i can insert it into 1 table and do a search.

Thanks for your help.

H


Posted by Aladin Akyurek on November 15, 2001 9:01 AM

See: 6009.html (NT)

Posted by Katt on November 15, 2001 11:02 AM

I can think of a couple of ways.
The easiest is, if your sheet1 is static just do an edit replace and replace space with nothing. Otherwise you could do :
=left(a3,3)&right(a3,8)
Good luck.
KS