software

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.

Ctrl H, in the find what box type

;

click in the replace with box

hold down alt key and type 010 on the numpad

release alt

Replace--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;beginnerExceluserquot; gt; wrote in
message ...
gt; Can anyone explain how I could replace all the semi-colon's in a column
gt; with
gt; the equivalent of an alt enter so that each value will appear on one line
gt; within the cell?
gt;
gt; I have a list of names that are separated by ; and I want to have each
gt; name
gt; on a separate line in the cell, without creating a new cell or row. So I
gt; start out with
gt;
gt; Smith, J;Smithe, C;Smythe, E
gt;
gt; and I want to end up with:
gt;
gt; Smith, J
gt; Smither, C
gt; Smythe, E
gt;
gt; I would need to do this for all the cells in a particular column so if
gt; there
gt; is a way to repeat it that would be great. Each cell has a different
gt; number
gt; of names so I'd like something that looks in each cell in the column for a
gt; ;
gt; and replaces it with a alt enter and moves on to the next cell to perform
gt; the
gt; same task.
gt;
gt; It seems like a simple task but it's driving me crazy, I'm doing it
gt; manually
gt; and I know there's a better way.
gt;
gt; PS, the simpler the better, I don't know a lot about Macros and such.
gt;
gt; 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?

quot;Peo Sjoblomquot; wrote:

gt; Ctrl H, in the find what box type
gt;
gt; ;
gt;
gt; click in the replace with box
gt;
gt; hold down alt key and type 010 on the numpad
gt;
gt; release alt
gt;
gt; Replace
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;beginnerExceluserquot; gt; wrote in
gt; message ...
gt; gt; Can anyone explain how I could replace all the semi-colon's in a column
gt; gt; with
gt; gt; the equivalent of an alt enter so that each value will appear on one line
gt; gt; within the cell?
gt; gt;
gt; gt; I have a list of names that are separated by ; and I want to have each
gt; gt; name
gt; gt; on a separate line in the cell, without creating a new cell or row. So I
gt; gt; start out with
gt; gt;
gt; gt; Smith, J;Smithe, C;Smythe, E
gt; gt;
gt; gt; and I want to end up with:
gt; gt;
gt; gt; Smith, J
gt; gt; Smither, C
gt; gt; Smythe, E
gt; gt;
gt; gt; I would need to do this for all the cells in a particular column so if
gt; gt; there
gt; gt; is a way to repeat it that would be great. Each cell has a different
gt; gt; number
gt; gt; of names so I'd like something that looks in each cell in the column for a
gt; gt; ;
gt; gt; and replaces it with a alt enter and moves on to the next cell to perform
gt; gt; the
gt; gt; same task.
gt; gt;
gt; gt; It seems like a simple task but it's driving me crazy, I'm doing it
gt; gt; manually
gt; gt; and I know there's a better way.
gt; gt;
gt; gt; PS, the simpler the better, I don't know a lot about Macros and such.
gt; gt;
gt; gt; Thank you.
gt;
gt;
gt;

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 toolsgt;macrogt;record new macro,
when prompted store the macro in your personal macro workbook and give it a
name like quot;replace_semicolquot;, 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:=quot;;quot;, Replacement:=quot;quot; amp; Chr(10) amp; quot;quot;, 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:=quot;;quot;, Replacement:=quot;quot; amp; Chr(10) amp; quot;quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range(quot;A1quot;).Select

Application.DisplayAlerts = False

End SubHTH

Peo

quot;beginnerExceluserquot; gt; wrote in
message ...
gt; Peo, thank you very much, I knew there was a simple way to do this. Is
gt; there
gt; anywhere that references how to perform these type of operations or is
gt; this
gt; something you learn from experience?
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Ctrl H, in the find what box type
gt;gt;
gt;gt; ;
gt;gt;
gt;gt; click in the replace with box
gt;gt;
gt;gt; hold down alt key and type 010 on the numpad
gt;gt;
gt;gt; release alt
gt;gt;
gt;gt; Replace
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;beginnerExceluserquot; gt; wrote
gt;gt; in
gt;gt; message ...
gt;gt; gt; Can anyone explain how I could replace all the semi-colon's in a column
gt;gt; gt; with
gt;gt; gt; the equivalent of an alt enter so that each value will appear on one
gt;gt; gt; line
gt;gt; gt; within the cell?
gt;gt; gt;
gt;gt; gt; I have a list of names that are separated by ; and I want to have each
gt;gt; gt; name
gt;gt; gt; on a separate line in the cell, without creating a new cell or row. So
gt;gt; gt; I
gt;gt; gt; start out with
gt;gt; gt;
gt;gt; gt; Smith, J;Smithe, C;Smythe, E
gt;gt; gt;
gt;gt; gt; and I want to end up with:
gt;gt; gt;
gt;gt; gt; Smith, J
gt;gt; gt; Smither, C
gt;gt; gt; Smythe, E
gt;gt; gt;
gt;gt; gt; I would need to do this for all the cells in a particular column so if
gt;gt; gt; there
gt;gt; gt; is a way to repeat it that would be great. Each cell has a different
gt;gt; gt; number
gt;gt; gt; of names so I'd like something that looks in each cell in the column
gt;gt; gt; for a
gt;gt; gt; ;
gt;gt; gt; and replaces it with a alt enter and moves on to the next cell to
gt;gt; gt; perform
gt;gt; gt; the
gt;gt; gt; same task.
gt;gt; gt;
gt;gt; gt; It seems like a simple task but it's driving me crazy, I'm doing it
gt;gt; gt; manually
gt;gt; gt; and I know there's a better way.
gt;gt; gt;
gt;gt; gt; PS, the simpler the better, I don't know a lot about Macros and such.
gt;gt; gt;
gt;gt; gt; Thank you.
gt;gt;
gt;gt;
gt;gt;
Thank you, I will give the macro a try.

quot;Peo Sjoblomquot; wrote:

gt; Experience and spending a lot of time in excel newsgroups, my site is still
gt; under construction but I hope to be able to put a tips and tricks section
gt; for things like these. Even if you don't know any about macros you can
gt; start to learn by record macro while you do this.
gt; select a cell where you want to do this, do toolsgt;macrogt;record new macro,
gt; when prompted store the macro in your personal macro workbook and give it a
gt; name like quot;replace_semicolquot;, click OK. Do the action, stop the macro. Next
gt; time just select
gt; the cells and run the macro by either pressing Alt F8 and double click the
gt; macro name or put a button and assign a macro to the button, then click the
gt; button. The record code should look like
gt;
gt; Cells.Replace What:=quot;;quot;, Replacement:=quot;quot; amp; Chr(10) amp; quot;quot;, LookAt:=xlPart,
gt; SearchOrder _
gt; :=xlByRows, MatchCase:=False, SearchFormat:=False,
gt; ReplaceFormat:=False
gt;
gt; then you might want to add a few lines so it isn't visible when it runs like
gt;
gt; Option Explicit
gt;
gt; Sub replace_semicol()
gt;
gt; Application.DisplayAlerts = False
gt;
gt; Cells.Replace What:=quot;;quot;, Replacement:=quot;quot; amp; Chr(10) amp; quot;quot;, LookAt:=xlPart,
gt; SearchOrder _
gt; :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
gt; Range(quot;A1quot;).Select
gt;
gt; Application.DisplayAlerts = False
gt;
gt; End Sub
gt;
gt;
gt; HTH
gt;
gt;
gt;
gt;
gt; Peo
gt;
gt; quot;beginnerExceluserquot; gt; wrote in
gt; message ...
gt; gt; Peo, thank you very much, I knew there was a simple way to do this. Is
gt; gt; there
gt; gt; anywhere that references how to perform these type of operations or is
gt; gt; this
gt; gt; something you learn from experience?
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; Ctrl H, in the find what box type
gt; gt;gt;
gt; gt;gt; ;
gt; gt;gt;
gt; gt;gt; click in the replace with box
gt; gt;gt;
gt; gt;gt; hold down alt key and type 010 on the numpad
gt; gt;gt;
gt; gt;gt; release alt
gt; gt;gt;
gt; gt;gt; Replace
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; nwexcelsolutions.com
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;beginnerExceluserquot; gt; wrote
gt; gt;gt; in
gt; gt;gt; message ...
gt; gt;gt; gt; Can anyone explain how I could replace all the semi-colon's in a column
gt; gt;gt; gt; with
gt; gt;gt; gt; the equivalent of an alt enter so that each value will appear on one
gt; gt;gt; gt; line
gt; gt;gt; gt; within the cell?
gt; gt;gt; gt;
gt; gt;gt; gt; I have a list of names that are separated by ; and I want to have each
gt; gt;gt; gt; name
gt; gt;gt; gt; on a separate line in the cell, without creating a new cell or row. So
gt; gt;gt; gt; I
gt; gt;gt; gt; start out with
gt; gt;gt; gt;
gt; gt;gt; gt; Smith, J;Smithe, C;Smythe, E
gt; gt;gt; gt;
gt; gt;gt; gt; and I want to end up with:
gt; gt;gt; gt;
gt; gt;gt; gt; Smith, J
gt; gt;gt; gt; Smither, C
gt; gt;gt; gt; Smythe, E
gt; gt;gt; gt;
gt; gt;gt; gt; I would need to do this for all the cells in a particular column so if
gt; gt;gt; gt; there
gt; gt;gt; gt; is a way to repeat it that would be great. Each cell has a different
gt; gt;gt; gt; number
gt; gt;gt; gt; of names so I'd like something that looks in each cell in the column
gt; gt;gt; gt; for a
gt; gt;gt; gt; ;
gt; gt;gt; gt; and replaces it with a alt enter and moves on to the next cell to
gt; gt;gt; gt; perform
gt; gt;gt; gt; the
gt; gt;gt; gt; same task.
gt; gt;gt; gt;
gt; gt;gt; gt; It seems like a simple task but it's driving me crazy, I'm doing it
gt; gt;gt; gt; manually
gt; gt;gt; gt; and I know there's a better way.
gt; gt;gt; gt;
gt; gt;gt; gt; PS, the simpler the better, I don't know a lot about Macros and such.
gt; gt;gt; gt;
gt; gt;gt; gt; Thank you.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;