Html Tags for Specific Words in a Cell

[cyberjaze]

Hi Everyone,

I have problem putting up html tags to every specific word in a cell. Let's say this praise.

JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?
MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!

The above is a conversation and I'd like to put <'b> <'/b> at the beginning and end to make the talking character's name in bold format when i uploaded it in the internet.

I target this result:

<'b>JUAN<'/b>: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?
<'b>MRS:<'/b> Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!

What I am thinking is replace first the : by the tag <'/b> (using usual excel feature) then add <'b> at the beginning of a word that contains <'/b>

That's the problem. How can I replace every word in a cell that contains the <'/b>. Or if there is a better idea, it will be much appreciated.

Hope someone can help me with this. Thank you.

 

[circledchicken]

Hi,

Try this (although I'm not sure its exactly what you want):

="<'b>"&SUBSTITUTE(A1,":","<'b>:")

 

[cyberjaze]

Hi,

Thank you for your reply.

Your formula will work if I plan to put the tag in each cell.

It's not actually what I am doing.

I need to put the tags <'b><'/b> in two or more words inside a single cell.

Thanks anyway.

*Please note that I will be working on many cells so manually doing it isn't so practical.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming the character names are single words or joined like Bob_Jones, you could try this.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> AddBoldTags(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "(\b\w+"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    AddBoldTags = RegEx.Replace(s, "<'b>$1<'/b>")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

Excel Workbook
	B	C
2	JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!	JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!
3	Bob_Jones: HelloJill:Hi Bobbig_bad_ben: Goodbye	Bob_Jones: HelloJill:Hi Bobbig_bad_ben: Goodbye
Add HTML Tags

 

[cyberjaze]

Hi Peter_SSs,

That's working GREAT! Thank you very much. It's so quick here .

By the way, I actually be going to use different sort of tags. The code was very very helpful already but if it would allow. I'm thinking if we could modify the code a little to make formula a little like:

=AddBoldTags(A1,'beginning tag','end tag')

Thank you very much

 

[Peter_SSs]

Hi Peter_SSs,

That's working GREAT! Thank you very much. It's so quick here .

By the way, I actually be going to use different sort of tags. The code was very very helpful already but if it would allow. I'm thinking if we could modify the code a little to make formula a little like:

=AddBoldTags(A1,'beginning tag','end tag')

Thank you very much

Try this version.

Function AddTags(s As String, starttag As String, endtag As String) As String
    Static RegEx As Object

    If RegEx Is Nothing Then
        Set RegEx = CreateObject("VBScript.RegExp")
        With RegEx
            .Global = True
            .Pattern = "(\b\w+:)"
        End With
    End If
    AddTags = RegEx.Replace(s, starttag & "$1" & endtag)
End Function

Excel Workbook
	B	C
2	JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!	JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!
3	Bob_Jones: HelloJill:Hi Bobbig_bad_ben: Goodbye	Bob_Jones: HelloJill:Hi Bobbig_bad_ben: Goodbye
Add HTML Tags (2)

 

[Biz]

Try this version.

Function AddTags(s As String, starttag As String, endtag As String) As String
    Static RegEx As Object
 
    If RegEx Is Nothing Then
        Set RegEx = CreateObject("VBScript.RegExp")
        With RegEx
            .Global = True
            .Pattern = "(\b\w+:)"
        End With
    End If
    AddTags = RegEx.Replace(s, starttag & "$1" & endtag)
End Function

Excel Workbook
	B	C
2	JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!	JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin?MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh!
3	Bob_Jones: HelloJill:Hi Bobbig_bad_ben: Goodbye	Bob_Jones: HelloJill:Hi Bobbig_bad_ben: Goodbye
Add HTML Tags (2)

very nice m8
Biz

 

[cyberjaze]

Thank you very much Peter_SSs... You're such a genius.

Exactly what I was looking for