Find/replace text in D-F from B

[richiwatts]

First le me confess that I posted this on another forum but the answer I got required knowledge above my head.

I have a list of names in column B
In columns D-F I have sentences and the person's name is somewhere in the sentence.
I need to be able to surround the name with teh html code <b> and </b>

John Smith
Did you see John Smith at the party?

Would give me
Did you see <b>John Smith</b> at the party?
Could that be done with a Macro?

Rich

 

[TonyJollans]

Just for clarification .. you have a name in B2, say, and sentences in D2, E2 and F2, containing the name? If so you don't need a macro - a simple formula in, say, G2, will do it ...

[tt]=SUBSTITUTE(D2;$B2;"<b>"&$B2&"</b>")[/tt]

and copy across to H2 and I2, and copy down as far as you need


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[richiwatts]

Thanks that worked nicely

 

[richiwatts]

Any way of it not having to be case sensitive?

John Smith

John Smith (finds)
john smith (doesn't find)
john Smith (doesn't find)

 

[Andrzejek]

I tried TonyJollans's sollution, but I got an error (The formula you typed contains an error.). I must be doing something wrong.

But this is what I came up with, a lot longer but it works for me:
[tt]
=IF(SEARCH(UPPER(B2),UPPER(D2),1),LEFT(D2,SEARCH(UPPER(B2),UPPER(D2),1)-1) & "<b>" & B2 & "</b>" & MID(D2,SEARCH(UPPER(B2),UPPER(D2),1) + LEN(B2),500))[/tt]

It deals only when you have "JoHn sMyTh" in cell B2 and "Did you see JohN smiTh at the party?" in D2

Have fun.

---- Andy

 

[TonyJollans]

Sorry, Andy,

Because of my international settings, I use semicolons instead of commas (I suppose I could change the setting but I never think to do it).

Case sensitivity can make life difficult, but as SEARCH is case-insensitive you don't need all those UPPERs. Adding a check in case the name isn't found, and keeping the casing from the original string, I get this ..

[tt]=IF(ISERR(SEARCH($B$2;D2));D2;LEFT(D2;SEARCH($B$2;D2)-1)&"<b>"&MID(D2;SEARCH($B$2;D2);LEN($B$2))&"</b>"&RIGHT(D2;LEN(D2)-SEARCH($B$2;D2)-LEN($B$2)))[/tt]

.. or this if you have US settings ..

[tt]=IF(ISERR(SEARCH($B$2,D2)),D2,LEFT(D2,SEARCH($B$2,D2)-1)&"<b>"&MID(D2,SEARCH($B$2,D2),LEN($B$2))&"</b>"&RIGHT(D2,LEN(D2)-SEARCH($B$2,D2)-LEN($B$2)))[/tt]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[richiwatts]

Guys, where can I change the language settings used?
I am on a Swedish machine and to get Tony's first solution to work all I had to use was theSwedish version of
=SUBSTITUTE

I found a list of all the function translations here

http://www.piuha.fi/excel-function-name-translation/index.php?page=svenska-english.html

which gives me
=OM(Ä(SÖK($B$2;D2));D2;VÄNSTER(D2;SÖK($B$2;D2)-1)&"<b>"&EXTEXT(D2;SÖK($B$2;D2);LÄNGD($B$2))&"</b>"&HÖGER(D2;LÄNGD(D2)-SÖK($B$2;D2)-LÄNGD($B$2)))

Results just says
#NAMN?

 

[TonyJollans]

I'm afraid that website appears to have some errors. Try this ...

[tt]OM(ÄRF(SÖK($B$2;D2));D2;VÄNSTERPIL(D2;SÖK($B$2;D2)-1)&"<b>"&MITT(D2;SÖK($B$2;D2);LÄNGD($B$2))&"</b>"&HÖGERPIL(D2;LÄNGD(D2)-SÖK($B$2;D2)-LÄNGD($B$2)))[/tt]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[richiwatts]

Now it just copies over column D but doesn't surround any names with <b> and </b>

 

[TonyJollans]

That suggests that [tt]SÖK($B$2;D2)[/tt] is returning an error. Can you put that in a cell by itself and see what you get?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[richiwatts]

I get #VÄRDEFEL!
valueerror in English ??

 

[TonyJollans]

Yes - [tt]#VALUE![/tt] in English.

That means that B2 is not found in D2. What - exactly, including spaces - is in the 2 cells?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[richiwatts]

OK, I tried a different row =SÖK($B$25;D25)where I knew the text was exactly the same (even casing) and it returned the number 35

In column B
Strawberry Cactus

In Column D
We stayed in the Strawberry Cactus rooms for 2 nights.

Result in column G
We stayed in the Strawberry Cactus rooms for 2 nights.

Missing <b> and </b>

 

[TonyJollans]

It works for me, but this may be partly my fault! Can you make sure that all the references to column B have the correct row number (I put [tt]$B$2[/tt] in the formula I posted, when [tt]$B2[/tt] would have been better.

If you select cell G2, and highlight the first [blue][tt]SÖK($B$25;D25)[/tt][/blue] (that comes immediately after [tt]ÄRF([/tt] and press F9, what do you see?

Other than that I find it interesting that you get 35 where I get 18. It rather seems like you may have your text encoded as UCS-2. I don't do enough in Excel these days to know what may trigger this (or even if my guess is right), and I'm not sure whether this matters, or what difference it may make - I just find it interesting!

For the record I am using Excel 2010 SP1 64-bit with Swedish UI (installed in Norwegian) on Windows 7 with an English UI (installed in English).

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[richiwatts]

OK, let me explain as I may have been doing things wrong.
The first couple of rows don't actually have an exact match

I am pasting your code into G1 and then dragging down but it keeps refering to $B$2

Look what happens on row 12

=OM(ÄRF(SÖK($B$2;D12));D12;VÄNSTERPIL(D12;SÖK($B$2;D12)-1)&"<b>"&MITT(D12;SÖK($B$2;D12);LÄNGD($B$2))&"</b>"&HÖGERPIL(D12;LÄNGD(D12)-SÖK($B$2;D12)-LÄNGD($B$2)))

However, now when I change all the $B$2 to $B2 and drag down the numbers are correct but for the rows where there are matches I now just get

#NAMN?

Is there a way to PM you the file? Or does the above say anything more?

I am using Swedish Excel 2003.

 

[TonyJollans]

E-mail me: Tony at Jollans dot com

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Well this IS the VBA forum, so here goes with a VBA User Defind Function, used on the sheet, just as you would use any other formula. Reference s1 to the string you bant BOLDED and s2 to the full string...

Function BoldString(s1 As String, s2 As String) As String
    Dim a, i As Integer, b, j As Integer, sTest1 As String, sTest2 As String
    Const BOLD_ST = "<b>"
    Const BOLD_EN = "</b>"
    Const SPACE = " "
    
    sTest1 = ""
    a = Split(s1, " ")
    For i = 0 To UBound(a)
        sTest1 = sTest1 & UCase(a(i))
    Next
    
    b = Split(s2, " ")
    For j = 0 To UBound(b)
        sTest2 = ""
        For i = 0 To UBound(a)
            If j + i <= UBound(b) Then _
                sTest2 = sTest2 & UCase(b(j + i))
        Next
        If sTest1 = sTest2 Then
            BoldString = BoldString & BOLD_ST
            For i = 0 To UBound(a)
                BoldString = BoldString & b(j + i) & SPACE
            Next
            BoldString = BoldString & BOLD_EN & SPACE
            j = j + UBound(a)
        Else
            BoldString = BoldString & b(j) & SPACE
        End If
    Next
    BoldString = Left(BoldString, Len(BoldString) - 1)
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[richiwatts]

Hi Tony,

Just opened the 2003 version you created in the English version and my Swedish version translated the function straight away. I copied the resulting function into my larger working file and it worked perfectly.
This is what my Swedish version created from your English version. I am sure I tried removing the PIL bit but that was before I tried changing $B$2 to $B2

=OM(ÄRF(SÖK($B2;D2));D2;VÄNSTER(D2;SÖK($B2;D2)-1)&"<b>"&EXTEXT(D2;SÖK($B2;D2);LÄNGD($B2))&"</b>"&HÖGER(D2;LÄNGD(D2)-SÖK($B2;D2)-LÄNGD($B2)))

This is what you sent me earlier. It looks like the EXTEXT in your new version is also different.
OM(ÄRF(SÖK($B$2;D2));D2;VÄNSTERPIL(D2;SÖK($B$2;D2)-1)&"<b>"&MITT(D2;SÖK($B$2;D2);LÄNGD($B$2))&"</b>"&HÖGERPIL(D2;LÄNGD(D2)-SÖK($B$2;D2)-LÄNGD($B$2)))

SkipVought, thanks for jumping in at this late stage. I wasn't really sure what to do with what you provided. I tried to run it as a Macro but I assume that wasn't right.


But SkipVought, Andrzejek thank you very much much for trying to help.

TonyJollans - I don't know how much to thank you.

I respect your help so much guys. Thanks all.

 

[SkipVought]

I wasn't really sure what to do with what you provided. I tried to run it as a Macro but I assume that wasn't right.

User Defind Function, used on the sheet, just as you would use any other formula. Reference s1 to the string you want BOLDED and s2 to the full string

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TonyJollans]

Sorry about the mix up with the extra "$", but glad it's all working, now.

It is interesting: it seems as though 2003 and 2010 have some different translations. As I said in my e-mail, I don't have Swedish installed on 2003, but that may be about to change, as some investigation is needed! I do know a Swedish Excel MVP who may be able to help (speaking Swedish could well be an advantage!).



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website