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

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

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

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