Text to columns

[Guest]

I am trying to split 700 names with phone numbers. They are in column A.
There are hyphens between the names and phone numbers. Do I have to replace
it with a coma and if so can i do the first one and copy it to do the rest or
do i have to do each one individually. I not really caught on to this posting
yet, so please accept my thanks in advance. Any help is very much appreciated.
ie John Doe - 250-555-5555

 

[T. Valko]

It looks like you have name space - space number.

Try this:

Select the range of cells in question
Goto the menu Edit>Replace
Find what: <space>-<space>
That is, type in a space then the dash then another space
Replace with: , (type in a comma)
Replace All
Close

With those cells still selected
Goto the menu Data>Text to Columns
Delimited>Next
Comma>Finish

Biff
Microsoft Excel MVP

 

[Max]

Biff

Microsoft Excel MVP

Congratulations, Biff ! Cheers.

---

 

[Guest]

Well earned, well deserved. Congratulations.

 

[Guest]

I think I did everything you requested but i get a message " cannot find the
data your searching for"

 

[Guest]

Another way - select your column - making sure that you have a couple of
empty columns to the right of the one selected. click on "text to columns".
Choose "Delimited", then click on the next button. in the next dialog box,
there is a list of Delimiters on the left. Check the box in front of "other"
and then put in a hyphen. click next, then finish.

 

[Guest]

Thank-you that worked. But I would like to keep my phone number in one column
if possible. I tried to regrouped the two columns with the phone number, but
I am not having any success.

 

[T. Valko]

Thank you, Max.

Hopefully, I haven't lowered the standard!

Biff
Microsoft Excel MVP

 

[T. Valko]

Thanks, Jerry!

Biff
Microsoft Excel MVP

Well earned, well deserved. Congratulations.

 

[gls858]

Thank-you that worked. But I would like to keep my phone number in one column
if possible. I tried to regrouped the two columns with the phone number, but
I am not having any success.

Assuming you still have an original try using text to columns and
leaving it as fixed width you can then place your dividers where you
need them. If you place the dash between the name and phone number in
it's own column you can just mark it not to import or just delete it later.

gls858

 

[T. Valko]

Ok, it might be that those spaces are not standard char 32 spaces. They
might be char 160 non breaking spaces. In the Edit>Replace operation:

Find what: hold down the ALT key and using the *numeric keypad* type 0160
let up on the ALT key then type the dash then hold down the ALT key and
using the *numeric keypad* type 0160

Replace with: , (comma)
Replace All
Close

Then do the text to columns

Biff
Microsoft Excel MVP

 

[T. Valko]

Assuming you still have an original try using text to columns and leaving
it as fixed width you can then place your dividers where you need them. If
you place the dash between the name and phone number in it's own column
you can just mark it not to import or just delete it later.

gls858

Assuming that they have a list, not just a single cell.

That would work *if* all the names were the same length. Won't work if you
have:

John Doe - 250-555-5555
William Johnston - 412-555-1212

Biff
Microsoft Excel MVP

 

[gls858]

Assuming that they have a list, not just a single cell.

That would work *if* all the names were the same length. Won't work if you
have:

John Doe - 250-555-5555
William Johnston - 412-555-1212

Biff
Microsoft Excel MVP

You're right. Sorry about that. I did exactly as you indicated I tested
on one cell. Almost all the files I bring into Excel are fixed length
flat files. I knew better just forgot for the moment

gls858

 

[Guest]

If using the hyphen as the deliminator separates the two parts of your phone
numbers, you can put them back together fairly easily. make sure there is an
empty column to the right of the two columns with the phone number and enter
=(cell&â€-“&cell) in the first cell of the empty column - for instance,
=(A1&"-"&B1) This will give you back your phone numbers with the hyphen.

 

[Guest]

for the 3 part phone number, just expand the formula
=(A1&"-"&B1&"-"&C1)

 

[Guest]

I forgot to mention - you have to then copy the formula by clicking in the
cell with the formula in it, then move the mouse over the lower right corner
until the cursor turns into a solid black cross, then click and drag the
formula all the way down to the bottom of your list.

 

[Dave Peterson]

I think that's a common feeling for everyone--that and the "did you really mean
me?" that goes with it <vbg>.

Congrats!

 

[T. Valko]

Thanks, Dave.

You're still the man!

Biff
Microsoft Excel MVP

 

[Guest]

Thank-you, I have both parts together. I would like to delete the the
columns in which they are separate, without affecting the finished product.
Is this possible.
BW

 

[Gord Dibben]

Add my congrats to this thread Biff.

Welcome aboard.

A few months ago you posted that becoming an MVP was your goal so what's your
next goal?


Gord