Dear Geniuses at Mr Excel,
I have been searching through various forums for some guidance/solution to an issue that I am facing but without quite reaching the desired outcome.
The problem:
I am attempting to concatenating text after cell "EXT" until it reaches text "GIA".
To expand on this, I have:
EXT
-2m wide frontage.
-approximately 190sqm vacant lot.
-no measure of soft landscaping.
GIA
area measured based on 1F
verified by GR
EXT
approximately 12.2sq.m.
etc. etc.
There are no blank cells between rows, the data is continuous with each entry in a new row within a column of circa 4000 entries.
My outcome:
I would like to concatenate text with the following outcome for the above examples:
EXT -2m wide frontage., -approximately 190sqm vacant lot., -no measure of soft landscaping.
GIA area measured based on 1F, verified by GR.
EXT approximately 12.2sq.m.
please note each entry should be separated by ", "
I have a formula which almost does what I want except I can only limit the cells to a range, which results in duplication of entries. I would like the formula to look for key words: "EXT" and "GIA" before concatenating the text that follows.
=IF(OR(ISNUMBER(SEARCH({"EXT","GIA"},$C$2:$C$4000))),CONCATENATE(C2,", ",C3,", ",C4,", ",C5,", ",C6, ", ",C7))
my data is in column C and I would like the result to be in cell G.
Thanks for all your help in advance!
I have been searching through various forums for some guidance/solution to an issue that I am facing but without quite reaching the desired outcome.
The problem:
I am attempting to concatenating text after cell "EXT" until it reaches text "GIA".
To expand on this, I have:
EXT
-2m wide frontage.
-approximately 190sqm vacant lot.
-no measure of soft landscaping.
GIA
area measured based on 1F
verified by GR
EXT
approximately 12.2sq.m.
etc. etc.
There are no blank cells between rows, the data is continuous with each entry in a new row within a column of circa 4000 entries.
My outcome:
I would like to concatenate text with the following outcome for the above examples:
EXT -2m wide frontage., -approximately 190sqm vacant lot., -no measure of soft landscaping.
GIA area measured based on 1F, verified by GR.
EXT approximately 12.2sq.m.
please note each entry should be separated by ", "
I have a formula which almost does what I want except I can only limit the cells to a range, which results in duplication of entries. I would like the formula to look for key words: "EXT" and "GIA" before concatenating the text that follows.
=IF(OR(ISNUMBER(SEARCH({"EXT","GIA"},$C$2:$C$4000))),CONCATENATE(C2,", ",C3,", ",C4,", ",C5,", ",C6, ", ",C7))
my data is in column C and I would like the result to be in cell G.
Thanks for all your help in advance!