[REQ] calculate the word before and after a designed one found in a cell

[Tetonne]

Hi Chandoo
thank you very much for this very interesting site and for these brilliant tips!
I have an Excel file with 2 columns :

- a first column A which contains one or n words.
- a second one (column B) with a text that contains the content of column A (it doesn't matter where)
both cells a link cos cell B contait word in cell A
ex:

Column A l Column B l Column C
maria l Mme théresa maria DUPONT DURAND l théresa maria DUPONT
jean-pierre l monsieur jean-pierre paul DURANT l monsieur jean-pierre paul

I would like to retrieve in column C the content of column A preceded by the word before and followed by the word after in column B.

how to do it?
Thanking you in advance

 

[vletm]

Tetonne
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Especially How to get the Best Results at Chandoo.org

 

[Anbuselvam K]

Check the below image results and attached file.

 

[bosco_yip]

Another formula solution and without helper columns

In C2, formula copied down :

=TRIM(SUBSTITUTE(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(" "&B2," "&A2&" ","@"&A2&"@")," ","</b><b>")&"</b></a>","//b[contains(.,'"&A2&"')]"),"@"," "))



Regards
Bosco

 

[Tetonne]

Showing thanks to Anbuselvam K and bosco_yip
i have a question about bosco_yip formula cos i'm using french excel. Translation give my that :
=SUPPRESPACE(SUBSTITUE(FILTRE.XML("<a><b>"&SUBSTITUE(SUBSTITUE(" "&B2;" "&A2&" ";"@"&A2&"@");" ";"</b><b>")&"</b></a>";"//b[contains(.,'"&A2&"')]");"@";" "))
but it dosn't work
i'm using excel 2019 for mac

 

[bosco_yip]

Showing thanks to Anbuselvam K and bosco_yip
i have a question about bosco_yip formula cos i'm using french excel. Translation give my that :
=SUPPRESPACE(SUBSTITUE(FILTRE.XML("<a><b>"&SUBSTITUE(SUBSTITUE(" "&B2;" "&A2&" ";"@"&A2&"@");" ";"</b><b>")&"</b></a>";"//b[contains(.,'"&A2&"')]");"@";" "))
but it dosn't work

There will be few reasons for English - French formula doesn't work.

1] The formula using a FILTERXML function of which available in Excel 2013 , Excel 2016 or Office 365 version.

2] "," convert to ";" problem
I noted that one (1) comma after ....[contains(.,'"&A2&"')].... haven't change to >> ...[contains(.;'"&A2&"')]....

3] Single quotes and double quotes

The screenshot maybe not show clear in the part of ....'"&A2&"'.....

it should read as >> Single quotes+Double quotes + "&" + "A2" + "&" +Double quotes +Single quotes

Regards
Bosco

 

[Tetonne]

thanks bosco_yip unfortunately it dies'n't work here on french office 2019 mac :-(
even if 1) is ok 2) was corrected and 3) are ok

 

[bosco_yip]

thanks bosco_yip unfortunately it dies'n't work here on french office 2019 mac :-(
even if 1) is ok 2) was corrected and 3) are ok

Here's a extraction from Microsoft regarding FILTERXML function and indicated that it is not available in Excel for Mac :



Regards
Bosco

 

[Tetonne]

^ lol better understand i'mm test on PC thanks again Bosco
regards
Tetonne

 

[Tetonne]

=SUPPRESPACE(SUBSTITUE(FILTRE.XML("<a><b>"&SUBSTITUE(SUBSTITUE(" "&B2;" "&A2&" ";"@"&A2&"@");" ";"</b><b>")&"</b></a>";"//b[contains(.;'"&A2&"')]");"@";" ")) doesn't work on office 2013 PC

 

[Marc L]

Salut !​

​

Thanks to post here only english formula as there are french Excel forums, you have the choice …​

​

French :​

comme en interne Excel est en anglais quelque soit sa version locale, côté VBA dans la fenêtre Exécution​

tu peux voir la version native de la formule de la cellule sélectionnée en validant cette commande : ?activecell.formula …​

Pour convertir une formule native (anglaise) dans la version locale il suffit de la valider​

dans cette fenêtre Exécution sans oublier de doubler chaque guillemet si elle en contient​

comme par exemple activecell.formula="=IF(A2=""OK"",SUM(B2:G2),0)" …​

​

English :​

as Excel is still in english whatever its local version, on VBA side in the Immediate window​

you can see the current cell original native formula just validating this command : ?activecell.formula …​

To convert a native formula (english) in your local version you just need to​

validate it in this Immediate window without forgetting to double each double quote if any​

like for example activecell.formula="=IF(A2=""OK"",SUM(B2:G2),0)" …​

​

 

[Tetonne]

can someone share the file please ? regards

 

[Tetonne]

I share with you 2 formulas :
A2= text
B2= text that must be found in A2

- the word before the one which is searched (C2) :
={IFERROR(RIGHT(LEFT(A2,IFERROR(SEARCH(B2,A2),0)-1),MATCH(" ",MID(LEFT(A2,IFERROR(SEARCH(B2,A2),0)-1),LEN(LEFT(A2,IFERROR(SEARCH(B2,A2),0)-1))-ROW($1:$34158),1),0)),LEFT(A2,IFERROR(SEARCH(B2,A2),0)-1))}

- the word after the one which is searched (D2)
=IFERROR(LEFT(MID(A2,IFERROR(SEARCH(B2,A2),0)+LEN(B2)+1,LEN(B2)+200),SEARCH(" ",MID(A2,IFERROR(SEARCH(B2,A2),0)+LEN(B2)+1,LEN(B2)+200),1)-1),"")

result = C2&" " &B2&" " &D2
it work on mac office 2019 and PC office 2013

May be a better user will be able to optimise those formulas (make them better)

I will have Office 2016 PC at the end of next week, i ll may be use :
=TRIM(SUBSTITUTE(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(" "&B2," "&A2&" ","@"&A2&"@")," ","</b><b>")&"</b></a>","//b[contains(.,'"&A2&"')]"),"@"," "))

 

[Excel Wizard]

Please try at F2
=TRIM(SUBSTITUTE(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(" "&TRIM(A2)&" ",B2,)," ","|")," ",REPT(" ",60)),FIND("|",SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(" "&TRIM(A2)&" ",B2,)," ","|")," ",REPT(" ",60)))-30,60),"|"," "&B2&" "))