Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace double spaces with Hard return
Hello,
I am using Excel 2003. I have a column of names, format first name last name. After each last name there are two spaces and then there is another first name last name. This carries on in most of the cells in the column. Is it possible to replace the double space with a hard return? Thanks in advance for your help. Ket London,UK |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace double spaces with Hard return
Ket -
· Highlight the column that you want to change. · Press Ctrl-H to bring up the Find/Replace dialog box · In the Find What box, press space bar twice · In the Replace With box, hold down the Alt key and type 0010 on the number pad (NOTE: You will not see anything entered in the Replace With box.) · Click Replace All and you should be done. - John Michl Ket wrote: Hello, I am using Excel 2003. I have a column of names, format first name last name. After each last name there are two spaces and then there is another first name last name. This carries on in most of the cells in the column. Is it possible to replace the double space with a hard return? Thanks in advance for your help. Ket London,UK |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace double spaces with Hard return
John,
Thanks for your prompt reply. Another teaser for you. If there are an unknown number of spaces between the last name and first name. Is there a way of searching for any spaces greater than 2 and placing the hard retrun just in front of the next first name? Hope this makes sense. On 26 Jun 2006 06:46:55 -0700, "John Michl" wrote: Ket - · Highlight the column that you want to change. · Press Ctrl-H to bring up the Find/Replace dialog box · In the Find What box, press space bar twice · In the Replace With box, hold down the Alt key and type 0010 on the number pad (NOTE: You will not see anything entered in the Replace With box.) · Click Replace All and you should be done. - John Michl Ket wrote: Hello, I am using Excel 2003. I have a column of names, format first name last name. After each last name there are two spaces and then there is another first name last name. This carries on in most of the cells in the column. Is it possible to replace the double space with a hard return? Thanks in advance for your help. Ket London,UK |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace double spaces with Hard return
Ket - I'm not aware of a simple way to do this without executing the
Find/Replace routine several times. With that said, the macro below does just that. I'll assume you know how to create a module and copy the macro to that module. Then in your spreadsheet, select the column of data, press Alt-F8 and run the macro. If you expect more than 10 consecutive spaces, change the 10 in the first line to the highest possible number. Hope that helps. - John Sub ReplaceSpaces() For i = 10 To 2 Step -1 ' Create a search string of spaces s = WorksheetFunction.Rept(" ", i) Selection.Replace What:=s, _ Replacement:="" & Chr(10) & "", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ ReplaceFormat:=False Next i End Sub Ket wrote: John, Thanks for your prompt reply. Another teaser for you. If there are an unknown number of spaces between the last name and first name. Is there a way of searching for any spaces greater than 2 and placing the hard retrun just in front of the next first name? Hope this makes sense. On 26 Jun 2006 06:46:55 -0700, "John Michl" wrote: Ket - · Highlight the column that you want to change. · Press Ctrl-H to bring up the Find/Replace dialog box · In the Find What box, press space bar twice · In the Replace With box, hold down the Alt key and type 0010 on the number pad (NOTE: You will not see anything entered in the Replace With box.) · Click Replace All and you should be done. - John Michl Ket wrote: Hello, I am using Excel 2003. I have a column of names, format first name last name. After each last name there are two spaces and then there is another first name last name. This carries on in most of the cells in the column. Is it possible to replace the double space with a hard return? Thanks in advance for your help. Ket London,UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find replace text or symbol with carriage return | New Users to Excel | |||
How do I make a hard return within a cell in Excel? | New Users to Excel | |||
How do I insert a "hard return" in a cell? | Excel Discussion (Misc queries) | |||
Is is possible to add a hard return to a merge cell formula? | Excel Discussion (Misc queries) | |||
hard return | New Users to Excel |