On Sep 2, 4:03*pm, Rob wrote:
I have a ranking report where the user selects a sort basis from a
drop-down control.

In one cell I spell out what the sort basis is using the following
formula:

="Retailer sort based on: "&list!A26

where the sort basis is listed in cell list!A26

And looks like this: Retailer sort based on: Sales Rate Index

However I prefer the appearance to be like this:
Retailer sort based on:
* *Sales Rate Index

In the days of Lotus I used the "^" to separate the text...but for the
life of me cannot figure a simple way to do this in Excel.

Any ideas?

TIA

Rob

This is a good function for parsing strings. Put a string into cell
1,1 and run this code.
The Split(TXT,sep) takes the string TXT and breaks it at each
occurrence of the marker "sep". The result
is a string array.

Andy

Dim txt As String
Dim x As Variant
Dim i As Long
Dim q As Integer


txt = Cells(1, 1).Value
x = Split(txt, " ")
For i = 0 To UBound(x)
Cells(2+i, 1) = x(i)
Next i