Extract Text From Column and Results To Another Cell

[gr8whthunter76]

Using formulas I have narrowed results down from multiple columns to a single column. It all comes down to one column and in cells I16 to I24. If certain conditions are met it will return just one name in one of those cells. What I can't figure out is how to extract that one name and place it in cell h7. In idiot terms, search I16:I24 and if there is any text in any of those cells then put that text in h7. There will only be one name in that column so there is no need to worry if multiples in those cells. I keep getting the #spill! That is a new error to me. I wish I knew this stuff better. Normally I can piece together google searches to get, but not this one.

 

[johnnyL]

How about:

Sub FindText()
'
    Dim Cell    As Range
'
    For Each Cell In Range("I16:I24")                                   ' Check each cell in the range
        If Len(Cell.Value) > 0 Then Range("H7").Value = Cell.Value      '   If a non blank cell is found Then save the value to H7
    Next                                                                ' Loop back
End Sub

 

[Rick Rothstein]

Since only one cell will have text, I would suggest this formula (for your XL365 version)...

=CONCAT(I16:I24)

 

[gr8whthunter76]

How about:

Sub FindText()
'
    Dim Cell    As Range
'
    For Each Cell In Range("I16:I24")                                   ' Check each cell in the range
        If Len(Cell.Value) > 0 Then Range("H7").Value = Cell.Value      '   If a non blank cell is found Then save the value to H7
    Next                                                                ' Loop back
End Sub

Holy Moses although you maybe right..........that is beyond my Excel knowledge to use VBA Code, my apologies!

 

[gr8whthunter76]

Since only one cell will have text, I would suggest this formula (for your XL365 version)...

=CONCAT(I16:I24)

That apparently is a function I need to read more on because that is not what I thought CONCAT did...........so easy now that I know. Thank you kind sir!!

 

[Rick Rothstein]

That apparently is a function I need to read more on because that is not what I thought CONCAT did...........so easy now that I know. Thank you kind sir!!

You are quite welcome. Another function related to CONCAT that you should look into it the TEXTJOIN function (it allows you to specify a delimiter for the concatenation).