Replace a word with hard return code

[Curious]

In one cell, there is a string of names seperated by ";", like "Base
Marketing & Advertising; Base Personal Technologies & Access; Base
Media Focus"

This cell is merged with several cells to make it a big cell
(irrelevant here).

I want to see in this cell look not like a string, but look like
seperate lines

Base Marketing & Advertising
Base Personal Technologies & Access
Base Media Focus

I thought that maybe I should format the cell as wrap text, and
replace ";" with hard recturn code CHAR(10), so that the string
becomes seperate lines. Is it possible? How do I do it? Is there
another way to get what I need?

Thanks In Advance.

H.Z.

 

[Dave Peterson]

Record a macro when you
select the range to fix
Edit|replace
what: ;
with: ctrl-j (hit and hold the control key while you hit the j key)
replace all

(you may want semicolon followed by a space character???)

You should see something like:

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


You could use this, too:
Selection.Replace What:="; ", Replacement:=vblf, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

[Skinman]

Why not simply put in a hard return, after typing Base Marketing&
Advertising. Press Option Enter to give you a hard return in the same cell.
The cell row height can be up to about 410, Enough to have severel rows of
info in the one cell.
Skinman

 

[Dave Peterson]

My bet is that the OP (not me!) already has that data in his worksheet. He/she
is not doing the data entry.