A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Text to columns



 
 
Thread Tools Display Modes
  #1  
Old July 2nd, 2007, 06:12 AM posted to microsoft.public.excel.newusers
B.W.
external usenet poster
 
Posts: 9
Default Text to columns

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
  #2  
Old July 2nd, 2007, 06:33 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Text to columns

It looks like you have name space - space number.

Try this:

Select the range of cells in question
Goto the menu EditReplace
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 DataText to Columns
DelimitedNext
CommaFinish

Biff
Microsoft Excel MVP

"B.W." wrote in message
...
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



  #3  
Old July 2nd, 2007, 09:47 AM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default Text to columns

Biff
Microsoft Excel MVP


Congratulations, Biff ! Cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #4  
Old July 2nd, 2007, 12:24 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 3,017
Default Text to columns

Well earned, well deserved. Congratulations.

"T. Valko" wrote:
Biff
Microsoft Excel MVP


  #5  
Old July 2nd, 2007, 04:42 PM posted to microsoft.public.excel.newusers
B.W.
external usenet poster
 
Posts: 9
Default Text to columns


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

"T. Valko" wrote:

It looks like you have name space - space number.

Try this:

Select the range of cells in question
Goto the menu EditReplace
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 DataText to Columns
DelimitedNext
CommaFinish

Biff
Microsoft Excel MVP

"B.W." wrote in message
...
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




  #6  
Old July 2nd, 2007, 04:52 PM posted to microsoft.public.excel.newusers
mfdou
external usenet poster
 
Posts: 7
Default Text to columns

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.

"B.W." wrote:


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

"T. Valko" wrote:

It looks like you have name space - space number.

Try this:

Select the range of cells in question
Goto the menu EditReplace
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 DataText to Columns
DelimitedNext
CommaFinish

Biff
Microsoft Excel MVP

"B.W." wrote in message
...
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




  #7  
Old July 2nd, 2007, 05:50 PM posted to microsoft.public.excel.newusers
B.W.
external usenet poster
 
Posts: 9
Default Text to columns

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.

"mfdou" wrote:

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.

"B.W." wrote:


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

"T. Valko" wrote:

It looks like you have name space - space number.

Try this:

Select the range of cells in question
Goto the menu EditReplace
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 DataText to Columns
DelimitedNext
CommaFinish

Biff
Microsoft Excel MVP

"B.W." wrote in message
...
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



  #8  
Old July 2nd, 2007, 06:31 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Text to columns

Thank you, Max.

Hopefully, I haven't lowered the standard!

Biff
Microsoft Excel MVP

"Max" wrote in message
...
Biff
Microsoft Excel MVP


Congratulations, Biff ! Cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---



  #9  
Old July 2nd, 2007, 06:32 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Text to columns

Thanks, Jerry!

Biff
Microsoft Excel MVP

"JLatham" HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Well earned, well deserved. Congratulations.

"T. Valko" wrote:
Biff
Microsoft Excel MVP




  #10  
Old July 2nd, 2007, 06:44 PM posted to microsoft.public.excel.newusers
gls858
external usenet poster
 
Posts: 473
Default Text to columns

B.W. wrote:
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.

"mfdou" wrote:

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.

"B.W." wrote:

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

"T. Valko" wrote:

It looks like you have name space - space number.

Try this:

Select the range of cells in question
Goto the menu EditReplace
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 DataText to Columns
DelimitedNext
CommaFinish

Biff
Microsoft Excel MVP

"B.W." wrote in message
...
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



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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.