Results 1 to 3 of 3

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

  1. #1
    Board Regular
    Join Date
    May 2018
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •