• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine 2 Cell value with "/" and to exclude repeated values

Ranjith M

New Member
Dear Experts ,

I need a Help in Excel that the query i have is to Combine 2 Cell value with "/" and to exclude repeated values

example :

cell A1 : I have a Pen to write
Cell B1 : have a Pencil to write
Cell C1 : i have a book to read

output i expected is

Cell D1 : i have a Pen/Pencil to write
Cell E1 or F1 : i have a Book to read

Please help to find a way to combine and to remove the similar words.
 
Try,

1] In Output 1 F2, formula copied down:

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99*(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))&"/"&IF(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),TRIM(LEFT(RIGHT(SUBSTITUTE(B2," ",REPT(" ",30)),90),30)),TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),300)))&" and "&TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),299))

2] In Output 1 I2, formula copied down:

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99*(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))&"/"&IF(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),TRIM(LEFT(RIGHT(SUBSTITUTE(B2," ",REPT(" ",30)),90),30)),TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),300)))&" and "&C2

82644
 

Attachments

  • sentence maker.xlsx
    11.7 KB · Views: 2
Back
Top