Cell range value in different cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2412474%22%20slang%3D%22en-US%22%3ECell%20range%20value%20in%20different%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412474%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22messagesWrapper-1sRNjr%20group-spacing-0%22%3E%3CDIV%20class%3D%22scroller-2LSbBU%20auto-Ge5KZx%20scrollerBase-289Jih%20disableScrollAnchor-3V9UtP%22%3E%3CDIV%20class%3D%22scrollerContent-WzeG7R%20content-3YMskv%22%3E%3CDIV%20class%3D%22scrollerInner-2YIMLh%22%3E%3CDIV%20class%3D%22message-2qnXI6%20groupStart-23k01U%20wrapper-2a6GCs%20compact-T3H92H%20zalgo-jN1Ica%22%3E%3CDIV%20class%3D%22contents-2mQqc9%22%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EHey%20guys.%20I%20have%20a%20question%20which%20I%20think%20should%20be%20able%20to%20be%20resolved.%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EI%20have%20this%20cell%20range%20I%20want%20to%20check%20for%20text.%20If%20cell%20range%20%3CSTRONG%3EA11%3C%2FSTRONG%3E%20to%20%3CSTRONG%3EZ15%3C%2FSTRONG%3E%20has%20text%20in%20it%2C%20display%20it%20underneath%20eachother%20on%20a%20different%20part%20of%20the%20page.%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EFor%20example%3A%20Cell%20%3CSTRONG%3EG12%3C%2FSTRONG%3E%20contains%20the%20text%20Workshop%20and%20cell%20%3CSTRONG%3EI15%3C%2FSTRONG%3E%20contains%20the%20text%20Next%20Week.%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EI%20want%20it%20to%20be%20displayed%20underneath%20eachother%20in%20a%20different%20cell%20range%20like%20this%3A%20Workshop%20Next%20Week%20So%20far%20I%20came%20up%20with%20the%20following%20solution%3A%20%3DIF(ISNUMBER(SEARCH(%22%22%2CA11%3AZ15))%2CA11%3AZ15%2C%22%22)%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EThis%20works%20almost%20like%20I%20want%20it%20to%2C%20except%20it%20%22copies%22%20the%20cell%20range%20and%20displays%20it%20from%20left%20to%20right.%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EThe%20photo's%20underneath%20here%20are%20for%20reference.%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-right%22%20image-alt%3D%22excel%20help%202.png%22%20style%3D%22width%3A%20271px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285938i01A12B7E6D1E47B7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22excel%20help%202.png%22%20alt%3D%22excel%20help%202.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-right%22%20image-alt%3D%22excel%20help.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285939i2290E3B7C6BB2644%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22excel%20help.png%22%20alt%3D%22excel%20help.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3EIs%20there%20any%20way%20I%20can%20get%20this%20to%20work%20or%20is%20this%20not%20possible%3F%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22markup-2BOw-j%20messageContent-2qWWxC%22%3ECheers!%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2412474%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2414488%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20range%20value%20in%20different%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2414488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1070360%22%20target%3D%22_blank%22%3E%40xiiRevenge%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXTJOIN(CHAR(10)%2CTRUE%2CA11%3AZ15)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hey guys. I have a question which I think should be able to be resolved.
 
I have this cell range I want to check for text. If cell range A11 to Z15 has text in it, display it underneath eachother on a different part of the page.
For example: Cell G12 contains the text Workshop and cell I15 contains the text Next Week.
 
I want it to be displayed underneath eachother in a different cell range like this: Workshop Next Week So far I came up with the following solution: =IF(ISNUMBER(SEARCH("",A11:Z15)),A11:Z15,"")
 
This works almost like I want it to, except it "copies" the cell range and displays it from left to right.
 
The photo's underneath here are for reference. 
 
excel help 2.pngexcel help.png
 
 
 
 
 
 
 
 
 
 
Is there any way I can get this to work or is this not possible?
 
Cheers!
1 Reply

@xiiRevenge 

Try

 

=TEXTJOIN(CHAR(10),TRUE,A11:Z15)