Excel adding Text before and after multiple strings within the same cell

INMD13

New member
Joined
Mar 15, 2013
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Mac Version 16.21.1
Hi All,


I have a cell that has multiple lines within a single cell. What I need to do is add <p> before and after every string within the Cell after each string separated by a return.


Original Cell Content


<strong><img class="alignleft wp-image-7685" style="margin: 5px;" title="mother's day infertility support" src="/blog/uploads/2014/05/1525639_10151806402881017_1083857534_n.jpg" alt="" width="230" height="230" /></a>By Dacina McNabb</strong>

"Ready or Not Here I come!"

I've been hearing the taunting singsong voice of yet another holiday that I dread.


New Cell Content



<p><strong><img class="alignleft wp-image-7685" style="margin: 5px;" title="mother's day infertility support" src="/blog/uploads/2014/05/1525639_10151806402881017_1083857534_n.jpg" alt="" width="230" height="230" /></a>By Dacina McNabb</strong><p>

<p>"Ready or Not Here I come!"<p>

<p>I've been hearing the taunting singsong voice of yet another holiday that I dread.<p>
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
How are the substrings separated now? Do they have a Carriage Return (i.e. ALT+ENTER used)?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Maybe try:

="<p>"&SUBSTITUTE(A1,CHAR(10),"<p>"&CHAR(10)&"<p>")&"<p>"

Where A1 contains the original
 

INMD13

New member
Joined
Mar 15, 2013
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Mac Version 16.21.1
yes the spaces are using ALT+ENTER. I tried the formula and it seems to work but I would like to not have the <p><p> in the spaces between strings. Is this possible? Thank for the help :)


<p><strong><img class="alignleft wp-image-7685" style="margin: 5px;" title="mother's day infertility support" src="/blog/uploads/2014/05/1525639_10151806402881017_1083857534_n.jpg" alt="" width="230" height="230" /></a>By Dacina McNabb</strong><p>
<p><p>
<p>"Ready or Not Here I come!"<p>
<p><p>
<p>I've been hearing the taunting singsong voice of yet another holiday that I dread.<p>
<p><p>
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try:

=SUBSTITUTE("<p>"&SUBSTITUTE(A1,CHAR(10),"<p>"&CHAR(10)&"<p>")&"<p>","<p><p>","")
 

INMD13

New member
Joined
Mar 15, 2013
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Mac Version 16.21.1
Thank you This worked perfectly !! :)
 
Top