Pick lists

[Guest]

Direction needed....
I have a column of data that I need to create a pick list from. The data is
seperated by spaces (non uniform). I can create a Named list from the column
but need to remove the spaces between items. Any ideas? One thought I had was
to push the data to another sheet and build it without the spaces, then
create the list. Is this possible? If so, does anyone have some pointers for
me? Or other ideas?

Thank you.

 

[Nigel]

Can you use TRIM to remove spaces at the end and beginning of the string?

If you need to remove intervening spaces then you could just iterate the
string looking for spaces and rebuild by adding back the before and after
parts of the string.

Function DeSpacer(xValue As String) As String
' function to remove all spaces within a string
DeSpacer = xValue
' remove end spaces
xValue = Trim(xValue)
Dim x As Integer, xL As Integer
' remove intervening spaces
For x = 1 To Len(xValue)
xL = InStr(xValue, " ")
If xL > 0 Then
xValue = Left(xValue, xL - 1) & Mid(xValue, xL + 1, Len(xValue) -
xL)
End If
Next
DeSpacer = xValue
End Function

To call this function use.....

mystring = DeSpacer(mystring)