UDF Request Concatenate(C1,D1) Placing D1 After a specific word if its in C1

[Tardisgx]

Input:C1 Mary went down the and hurt her head D1 hill

Desired Output: Mary went down the hill and hurt her head

Below is the best what I can describe the logic as:

(If C1 contains "keyword") (Separator " ") concatenate [D1] as "[C1...Keyword][Separator][D1] [Rest of C1]"

Below is the same description with my input and output

If C1 contains "the" Separator " " Concatenate "hill" as "Mary went down the hill and hurt her head"

In the above case the keyword is "the"

The Separator denotes what is required to precede D1 (in this case space is inserted before hill)

If the keyword is not found a result of #N/A or #VALUE is good.

*Ideally*
If the keyword is nothing "" concatenate at the start of the cell and the separator places itself after the keyword not before
A part of the formula would involve stating which instance of keyword you are referring to [If C1 contains more than one instance of "the"]

 

[Marcelo Branco]

See if this formula does what you need


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Keyword​

[/td][td]

Text​

[/td][td]

New Word​

[/td][td]

Output​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

the​

[/td][td]

Mary went down the and hurt her head​

[/td][td]

hill​

[/td][td]

Mary went down the hill and hurt her head​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

and​

[/td][td]

Mary went down and hurt her head​

[/td][td]

didn't​

[/td][td]

Mary went down and didn't hurt her head​

[/td][/tr]
[/table]

Formula in E2 copied down
=IFERROR(REPLACE(C2,SEARCH(B2&" ",C2)+LEN(B2&" "),0,D2&" "),"N/A")

Hope this helps

M.

 

[Marcelo Branco]

Maybe you really need a UDF to handle more complicated cases - keyword as the last word; keyword followed by a punctuation mark; etc ....
I'll create a tag for Rick Rothstein - no one better to create such a UDF

M.