Replace a word with hard return code
 

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.

Replace a word with hard return code
 

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


Curious wrote:

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

Replace a word with hard return code
 

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" wrote in message
...
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


Curious wrote:

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

Replace a word with hard return code
 

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

Skinman wrote:

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" wrote in message
...
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


Curious wrote:

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

--

Dave Peterson