PDA

View Full Version : Find and replace words that are formatted with tags



AA_20069
08-04-2014, 06:44 AM
Hi All,

Ive done a search already, and the results don't seem to resolve a problem i have.

Within my spreadsheet on columns G & H each cell contains a couple sentences with some words formatted

e.d. Go to this place and do this. Then afterwards submit this item. Something in bold and italics etc etc etc

what i would like to happen is that VBA(?) would then be able to identify the bold and format the wording as follows


Go to <b>this place</b> and <b>do this</b>. Then afterwards <b>submit this item</b>.Something <b><i>in italics</i></b>

This would need to happen on each cell in rows g & h?

any helps would be appreciated.

Many thanks.

westconn1
08-04-2014, 02:45 PM
you can try like this

Dim cel As Range, tmp as String
Dim bb As Boolean, bi As Boolean
For Each cel In Range("g:h") 'change to used range
If Not IsEmpty(cel) Then
For ch = 1 To cel.Characters.Count
If cel.Characters(ch, 1).Font.Bold And Not bb Then
bb = True
tmp = tmp & "<b>"
ElseIf Not cel.Characters(ch, 1).Font.Bold And bb Then
bb = False
tmp = tmp & "</b>"
End If
If cel.Characters(ch, 1).Font.Italic And Not bi Then
bi = True
tmp = tmp & "<i>"
ElseIf Not cel.Characters(ch, 1).Font.Italic And bi Then
bi = False
tmp = tmp & "</i>"
End If
If bb Then tmp = tmp & "</b>"
If bi Then tmp = tmp & "</i>"
cel.Value = tmp
tmp = tmp & cel.Characters(ch, 1)
Next
End If
Nexti did not test this so may contain error or typo, test before using, the cell result will no longer contain formatting, if you want to retain formatting as well as tags, you would need to use a range (cell) to hold temporary value, in place of string

AA_20069
08-05-2014, 12:20 AM
Hi westconn1,

Many thanks for your reply. This sort of does the trick; expect for the following:
1) it replaces the entire cell value (if it finds bold) with <b></b> rather then adding the tags around the phrase of the word i.e. <b>this part is bold tag me</b>, this part isnt leave me alone
2) its failing at line
tmp = tmp & cel.Characters(ch, 1) with the following error : Run-time Error '438' Object doesn't support this property or method.

Many thanks for your assitance in this

westconn1
08-05-2014, 04:06 AM
2. change to
tmp = tmp & cel.Characters(ch, 1).text

i changed the above line and tested the code on your string in first post correct result was obtained, except the order of bold and italic close tags together

i put the result in a different cell for testing purposes only
Go to <b>this place </b>and do this. Then afterwards <b>submit this item</b>. Something in <b><i>bold and italics</b></i> etc etc etc