none
Excel Conditional Concatation Help Please RRS feed

  • Question

  • Hello,

    I am struggling to find an answer to my question. I am looking to concatenate all of the cells that are in the same row in a range leading up to the cell that gives a specific value. The value is loaded into different columns in each row, so the value, lets say "Apple" falls into columns C through F depending on the row.

    For example,

    Row 1: I would like to eat a cold green apple on a tuesday(each word in different cell)

    Row 2: I would like an apple right now ("")

    How can I set it up the code so that every word leading up to Apple and including Apple can be concatenated into one column for the data?

    thank you!

    Friday, April 24, 2015 9:18 PM

Answers

  • Need to concatenate all of the values and then extract the left string up to the required text. Note the example below is actually one line but breaks in the post here.

    =LEFT(CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1," ",G1," ",H1," ",I1," ",J1," ",K1," ",L1),SEARCH("apple",CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1," ",G1," ",H1," ",I1," ",J1," ",K1," ",L1),1)+LEN("apple"))

    You could perform the concatenation in a separate cell and reference that cell in the formula like the following where A10 contains the concatenation formula only.

    =LEFT(A10,SEARCH("apple",A10,1)+LEN("apple"))


    Regards, OssieMac

    Saturday, April 25, 2015 4:06 AM
  • Hi Pruney21,

    For programing to accomplish that, you could use Range.Find method to file the specify value in the cell.

    To return or set a value of specified range, we could use Range.Value property.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 27, 2015 8:37 AM
    Moderator

All replies

  • Need to concatenate all of the values and then extract the left string up to the required text. Note the example below is actually one line but breaks in the post here.

    =LEFT(CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1," ",G1," ",H1," ",I1," ",J1," ",K1," ",L1),SEARCH("apple",CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1," ",G1," ",H1," ",I1," ",J1," ",K1," ",L1),1)+LEN("apple"))

    You could perform the concatenation in a separate cell and reference that cell in the formula like the following where A10 contains the concatenation formula only.

    =LEFT(A10,SEARCH("apple",A10,1)+LEN("apple"))


    Regards, OssieMac

    Saturday, April 25, 2015 4:06 AM
  • Hi Pruney21,

    For programing to accomplish that, you could use Range.Find method to file the specify value in the cell.

    To return or set a value of specified range, we could use Range.Value property.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 27, 2015 8:37 AM
    Moderator