Replace a ; with alt+enter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone explain how I could replace all the semi-colon's in a column with
the equivalent of an alt+enter so that each value will appear on one line
within the cell?

I have a list of names that are separated by ; and I want to have each name
on a separate line in the cell, without creating a new cell or row. So I
start out with

Smith, J;Smithe, C;Smythe, E

and I want to end up with:

Smith, J
Smither, C
Smythe, E

I would need to do this for all the cells in a particular column so if there
is a way to repeat it that would be great. Each cell has a different number
of names so I'd like something that looks in each cell in the column for a ;
and replaces it with a alt+enter and moves on to the next cell to perform the
same task.

It seems like a simple task but it's driving me crazy, I'm doing it manually
and I know there's a better way.

PS, the simpler the better, I don't know a lot about Macros and such.

Thank you.
 
Peo, thank you very much, I knew there was a simple way to do this. Is there
anywhere that references how to perform these type of operations or is this
something you learn from experience?
 
Experience and spending a lot of time in excel newsgroups, my site is still
under construction but I hope to be able to put a tips and tricks section
for things like these. Even if you don't know any about macros you can
start to learn by record macro while you do this.
select a cell where you want to do this, do tools>macro>record new macro,
when prompted store the macro in your personal macro workbook and give it a
name like "replace_semicol", click OK. Do the action, stop the macro. Next
time just select
the cells and run the macro by either pressing Alt + F8 and double click the
macro name or put a button and assign a macro to the button, then click the
button. The record code should look like

Cells.Replace What:=";", Replacement:="" & Chr(10) & "", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

then you might want to add a few lines so it isn't visible when it runs like

Option Explicit

Sub replace_semicol()

Application.DisplayAlerts = False

Cells.Replace What:=";", Replacement:="" & Chr(10) & "", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("A1").Select

Application.DisplayAlerts = False

End Sub


HTH




Peo
 
Thank you, I will give the macro a try.

Peo Sjoblom said:
Experience and spending a lot of time in excel newsgroups, my site is still
under construction but I hope to be able to put a tips and tricks section
for things like these. Even if you don't know any about macros you can
start to learn by record macro while you do this.
select a cell where you want to do this, do tools>macro>record new macro,
when prompted store the macro in your personal macro workbook and give it a
name like "replace_semicol", click OK. Do the action, stop the macro. Next
time just select
the cells and run the macro by either pressing Alt + F8 and double click the
macro name or put a button and assign a macro to the button, then click the
button. The record code should look like

Cells.Replace What:=";", Replacement:="" & Chr(10) & "", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

then you might want to add a few lines so it isn't visible when it runs like

Option Explicit

Sub replace_semicol()

Application.DisplayAlerts = False

Cells.Replace What:=";", Replacement:="" & Chr(10) & "", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("A1").Select

Application.DisplayAlerts = False

End Sub


HTH




Peo
Click to expand...
 
Back
Top