Display an array from one field.

[SilverSN95]

Hi, This may be a question you have seen before but after searching the forum I couldn't find anything quite related.
In my xls spreadsheet, I have a cell, call it A, that has this formula in it:
{='Exposure - GL'!F149:F154}.
This cell as I understand it contains an array, which for my purposes is all text. What I would like to do is display this in cell B with a macro that will write its contents in a fashion like
Text1, Text2, Text3
until the end of the array. Since this range is not necessarily full, it would be helpful to have the end of the array be specified by the first null value or something similar.

Thanks again for your help.

[nst1107]

I think this is what you are looking for.Option Explicit
Sub a()
Dim f As Range, c As Range
Dim txt As String
Set f = Sheet2.Range(Mid(Sheet1.Cells(1, 1).Formula, _
WorksheetFunction.Search("!", Sheet1.Cells(1, 1).Formula) + 1))
For Each c In f
If c = vbNullString Then Exit For
If txt = vbNullString Then txt = c Else txt = txt & ", " & c
Next
Sheet1.Cells(1, 2) = txt
End Sub

[mdmackillop]

As a UDF try

Function MyString(Data As Range)
Dim cel As Range, txt As String
For Each cel In Data
If cel = "" Then Exit For
txt = txt & cel & ", "
Next
MyString = Left(txt, Len(txt) - 2)
End Function


enter as =mystring(Sheet2!I8:I19)

[SilverSN95]

Thanks for the replies... In the first code, I'm not quite sure what is going on in this statement.
Set f = Sheet2.Range(Mid(Sheet1.Cells(1, 1).Formula, _
WorksheetFunction.Search("!", Sheet1.Cells(1, 1).Formula) + 1))

I tried changing the Set f to sourceWS.Range("B16") it only displays the first element in the array, which is also what is displayed in B16. I'm assuming I need a way to reference the actual array in the cell to get this to work, but I don't know how.
When I tried using the second function, I get #NAME? in the cell I type the equation in:=MyString(B16) where B16 is the cell holding the array.

[mdmackillop]

You need to put the code in a standard module.

[nst1107]

This statement
Set f = Sheet2.Range(Mid(Sheet1.Cells(1, 1).Formula, _

WorksheetFunction.Search("!", Sheet1.Cells(1, 1).Formula) + 1))
gets the address of the cells you want to concatenate from the formula in 'cell A' and sets f equal to that range of cells. You probably want to just change "Sheet2" to "Sheets("sourceWS")" and "Sheet1" to "Sheets("Exposure - GL")". Also, you will need to change "Cells(1, 1)" to reference the appropriate cell.