# Thread: UDF Request Concatenate(C1,D1) Placing D1 After a specific word if its in C1 Thanks:  1 Post #5116816 (1) Likes:  1 Post #5116816 (1)

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

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"]

2. ## Re: UDF Request Concatenate(C1,D1) Placing D1 After a specific word if its in C1

See if this formula does what you need

 B C D E 1 Keyword Text New Word Output 2 the Mary went down the and hurt her head hill Mary went down the hill and hurt her head 3 and Mary went down and hurt her head didn't Mary went down and didn't hurt her head

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

Hope this helps

M.

3. ## Re: UDF Request Concatenate(C1,D1) Placing D1 After a specific word if its in C1

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.