Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick lists
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick lists
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) -- Cheers Nigel "Ron" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pick from lists | Excel Worksheet Functions | |||
Using Pick Lists | Excel Worksheet Functions | |||
Pick Lists | Excel Programming | |||
Pick Lists | Excel Programming | |||
Pick Lists | Excel Programming |