 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007Text 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 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 > I am trying to split 700 names with phone numbers. They are in column > A. [quoted text clipped - 7 lines] > appreciated. > ie John Doe - 250-555-5555 > Biff > Microsoft Excel MVP Congratulations, Biff ! Cheers.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Thank you, Max. Hopefully, I haven't lowered the standard! Biff Microsoft Excel MVP >> Biff >> Microsoft Excel MVP > > Congratulations, Biff ! Cheers. I think that's a common feeling for everyone--that and the "did you really mean me?" that goes with it <vbg>. Congrats! > Thank you, Max. > [quoted text clipped - 13 lines] > > xdemechanik > > --- SignatureDave Peterson Thanks, Dave. You're still the man! Biff Microsoft Excel MVP >I think that's a common feeling for everyone--that and the "did you really >mean [quoted text clipped - 19 lines] >> > xdemechanik >> > --- Hi Biff Congratulations, very well deserved. I am also very happy to be joining you, and hope I can live up to expectations!!! SignatureRegards Roger Govier > Thanks, Dave. > [quoted text clipped - 26 lines] >>> > xdemechanik >>> > --- > I am also very happy to be joining you, and hope I can live up to > expectations!!! Hey Roger, subtle comment, but I didn't miss that ... My congratulations to you too! Cheers P/s: Is there a reference namelist of the new kids on the MVP-Excel block available ? Or, could someone kindly take a moment to list these new kids? SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- There's a privacy issue at work here. So I don't think you'll be seeing a list posted. But for those who choose to share, you can check: https://mvp.support.microsoft.com/communities/mvp.aspx or for excel: https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Micr osoft+Office+Excel > > I am also very happy to be joining you, and hope I can live up to > > expectations!!! [quoted text clipped - 10 lines] > xdemechanik > --- SignatureDave Peterson Dave, thanks for response/links > .. So I don't think you'll be seeing a list posted. No choice, resigned to this then. disappointed, though, that such occasions for us to rejoice/celebrate with new Excel MVPs should be stifled by privacy issues .. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > There's a privacy issue at work here. So I don't think you'll be seeing a > list [quoted text clipped - 4 lines] > or for excel: > https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Micr osoft+Office+Excel Thanks Max. Dave has given the reason for the lack of a list. I have not got round to setting up a Profile on the website yet, neither would I imagine have many other of the "newbie's" . SignatureRegards Roger Govier >> I am also very happy to be joining you, and hope I can live up to >> expectations!!! [quoted text clipped - 5 lines] > block available ? Or, could someone kindly take a moment to list these > new kids? > Dave has given the reason for the lack of a list. dang! <g> no choice, then. looks like I'll have to read deep into all postings by the regulars to "gather" such info. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Thanks Max. > > Dave has given the reason for the lack of a list. > I have not got round to setting up a Profile on the website yet, neither > would I imagine have many other of the "newbie's" . I'm having trouble figuring out just how to do that? SignatureBiff Microsoft Excel MVP > Thanks Max. > [quoted text clipped - 11 lines] >> available ? Or, could someone kindly take a moment to list these new >> kids? My congratulations also. Well deserved. There'll be a newcomer's meeting soon (another privacy thing) which you may have already received notice about - be sure and set that account up so you can attend. > Thanks Max. > [quoted text clipped - 11 lines] > > block available ? Or, could someone kindly take a moment to list these > > new kids? Congratulations, Roger! I had assumed that you were already A MVP but just didn't make it public. >hope I can live up to expectations!!! Yeah, I know what you mean. Harlan has already "tested" me. I guess I passed! Wooo hooo! <VBG> SignatureBiff Microsoft Excel MVP > Hi Biff > [quoted text clipped - 32 lines] >>>> > xdemechanik >>>> > --- 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 >I think that's a common feeling for everyone--that and the "did you really mean >me?" that goes with it <vbg>. [quoted text clipped - 18 lines] >> > xdemechanik >> > --- Thanks, Gord! >A few months ago you posted that becoming an MVP >was your goal so what's your next goal? To not embarrass myself as a MVP! SignatureBiff Microsoft Excel MVP > Add my congrats to this thread Biff. > [quoted text clipped - 33 lines] >>> > xdemechanik >>> > --- Not a realistic goal. Try something else like "To not embarrass myself as a MVP!.......too often"<g> Gord >Thanks, Gord! > >>A few months ago you posted that becoming an MVP >>was your goal so what's your next goal? Well earned, well deserved. Congratulations. > Biff > Microsoft Excel MVP Thanks, Jerry! Biff Microsoft Excel MVP > Well earned, well deserved. Congratulations. > >> Biff >> Microsoft Excel MVP I think I did everything you requested but i get a message " cannot find the data your searching for" > It looks like you have name space - space number. > [quoted text clipped - 27 lines] > > appreciated. > > ie John Doe - 250-555-5555 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. > I think I did everything you requested but i get a message " cannot find the > data your searching for" [quoted text clipped - 30 lines] > > > appreciated. > > > ie John Doe - 250-555-5555 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. > 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". [quoted text clipped - 36 lines] > > > > appreciated. > > > > ie John Doe - 250-555-5555 > 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 [quoted text clipped - 40 lines] >>>>> 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 >> 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 [quoted text clipped - 46 lines] > > 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 >>> 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 [quoted text clipped - 57 lines] > 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 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 > I think I did everything you requested but i get a message " cannot find > the [quoted text clipped - 33 lines] >> > appreciated. >> > ie John Doe - 250-555-5555 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. > 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: [quoted text clipped - 49 lines] > >> > appreciated. > >> > ie John Doe - 250-555-5555 for the 3 part phone number, just expand the formula =(A1&"-"&B1&"-"&C1) > 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 [quoted text clipped - 55 lines] > > >> > appreciated. > > >> > ie John Doe - 250-555-5555 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. > 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 [quoted text clipped - 55 lines] > > >> > appreciated. > > >> > ie John Doe - 250-555-5555 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 > 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 [quoted text clipped - 60 lines] > > > >> > appreciated. > > > >> > ie John Doe - 250-555-5555 Select your finished product, copy then click Edit/Paste Special/Values to convert the formulae to constants. Then delete the other columns you don't need (verify the paste special works before deleting the source). I also wanted to jump in and congratulate Biff and Roger on reaching their goals - very well deserved recognition! > 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. [quoted text clipped - 65 lines] > > > > >> > appreciated. > > > > >> > ie John Doe - 250-555-5555 Thanks, J! SignatureBiff Microsoft Excel MVP > Select your finished product, copy then click Edit/Paste Special/Values to > convert the formulae to constants. Then delete the other columns you [quoted text clipped - 92 lines] >> > > > >> > appreciated. >> > > > >> > ie John Doe - 250-555-5555 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.