XL2K: creating list of names and phone numbers

[ClaudCar]

have 4 spreadsheets with schedules. Have been requested to make a master
phone list from the schedule.
a cell has lastname, firstname, another cell has a phone # below above. is
there a way to do this in Excel 2K other than manually.
______________________
Claudia
(e-mail address removed)
Totus Tuus

 

[ClaudCar]

aaaacccckkkkk - found another formatting boooo boooo, alt-Enter to put a
return in a cell and put the phone number below the name in the same cell.
PLEASE tell me that there is a way to save this spreadsheet without
reinputting all the data. I KNEW I should have built it from the get go.
Now 6 months of info later it is too late ;-((
______________________
Claudia
Totus Tuus

 

[Gord Dibben]

Claudia

First, insert a column to the right of your address column.

Data>Text to Columns>Delimited

In the the "Other" box hold down ALT key and hit 0010 on the NUMPAD.

You won't see anything in the box, but click Next to see what you get.

It could be a 0013 also.

Gord Dibben Excel MVP

 

[Max]

Perhaps one possible way to salvage ..

Give the steps below a try
on a *test* copy of your file:

Supposing your data in col A
(from row1 down) looks like this:

In cell A1:
Lname1, Fname1
Phone#1

In cell A2:
Lname2, Fname2
Phone#2

etc

Put in B1:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),", "),","," "))
Copy B1 down

The above will remove the "Alt+Enter" and the commas,
viz col B will return as :

Lname1 Fname1 Phone#1
Lname2 Fname2 Phone#2
etc

Now select col B
Right-click > Copy
Right-click > Paste special > Values > OK

This converts the formulas in col B to values
to prepare for splitting below

With col B still selected
Click Data > Text to columns
Click "Finish"

The above will split the contents of col B into 3 parts:

Lname in col B
Fname in col C
Phone# in col D