How to split a phone number into two columns

[MCP2000]

I have an oddball request. I have a 9k line spreadsheet with phone numbers list as one 10-character string of digits. I need to separate the area code and 7-digit number. The 7-digit number needs to be moved to the adjaced column to the right.

I have tried writing the VBA for this, but I keep getting something terribly wrong, because it just locks-up Excel.

By the way, I am using Excel 2007.

TIA!

 

[starsky51]

You don't need VBA for this.
Assuming the strings are all the same length and they are in column A, you can use the function LEFT(A1,3) in column B and the function RIGHT(A1, 7) in column C.

 

[ettienne]

You can do the in Excel with some simple formulae:
=Left(A1,3)
=Right(A1,7)
assuming the number is in cell A1. Copy the formulae into 2 columns and it should be done in a second or 2.

 

[AnotherHiggins]

Or without even using a formula - have a loot at Dat > Text To Columns.

Select Fixed Width and put a separator after the third number.

That's all there is to it.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

that should have said "Data > Text To Columns"

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.