Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function equivalent to 'text to columns' (xp or 2000)

    I am trying to create a function that will allow me to seperate a cell into parts based on a separator. The results I would expect are:


    for the function defined as:

    function TextToColumns(cell,separator,part)
    'cell = cell containing text
    'separator= character that breaks up text
    'part = section of text between separators


    so for example if B2 contains "123;xy;abc"

    then:
    TextToColumns(B2,";",1)=123
    TextToColumns(B2,";",2)=xy
    TextToColumns(B2,";",3)=abc

    Each cell could have any number of parts (including 0)

    I am struggling with the code that will find every separator in the string and then return the characters between a given pair of separators

    Any Ideas will be appreciated

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Function equivalent to 'text to columns' (xp or 2000)

    Here is a megaformula:
    =MID(Cell,IF(part=1,0,FIND(CHAR(1),SUBSTITUTE(Cell &separator,separator,CHAR(1),part-1)))+1,(FIND(CHAR(1),SUBSTITUTE(Cell&separator,sep arator,CHAR(1),part))-IF(part=1,0,FIND(CHAR(1),SUBSTITUTE(Cell&separator ,separator,CHAR(1),part-1))))-1)

    If you are trying to create a user defined function, the logic is

    just go thru the "cell-string" character by character and compare each character to the the separator and count them. when you get to the "part-1"th start collecting the characters in a string. when you get to the "part"th separator stop collecting and you are done.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function equivalent to 'text to columns' (xp or 2000)

    Thanks for the ideas Steve, am impressed by the function. This is the vba I have come up with. Can anyone offer any improvements

    Function TextToColumns(cell As Range, separator As String, part As Integer)

    Dim MyChar As String 'each character in the cell range
    Dim CurrPart As Integer 'part being checked
    Dim Result As String 'required characters
    Dim Length As Integer 'length of cell
    Dim CharN As Integer 'current character number

    CurrPart = 1 'at least one part -so first character belongs to part 1
    Length = Len(cell)

    For CharN = 1 To Length
    MyChar = cell.Characters(CharN, 1).Text 'character

    If CurrPart = part And MyChar <> separator Then Result = Result & MyChar
    If MyChar = separator Then CurrPart = CurrPart + 1 '
    Next CharN

    TextToColumns = Result

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function equivalent to 'text to columns' (xp or 2000)

    Well, the easy way would be to use the "Text to columns" command on the Data menu. However, if you want a function, you could do something like this:

    <pre>Public Function TextToColumns(oCell As Range, strSeparator As String, iPart As Integer) As String
    Dim strResult As String, strWk As String
    Dim I As Integer
    strWk = oCell.Value & strSeparator
    For I = 1 To iPart
    strResult = ""
    If Len(strWk) = 0 Then Exit For
    strResult = Left(strWk, InStr(strWk, strSeparator) - 1)
    strWk = Right(strWk, Len(strWk) - InStr(strWk, strSeparator))
    Next I
    TextToColumns = strResult
    End Function
    </pre>

    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Function equivalent to 'text to columns' (xp or 2000)

    Legare,
    Your code is better than what I was thinking of. I guess I need to review my VBA functions: I hadn't realized there was "INSTR" so I was going to "Brute-force" and have the function do it.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function equivalent to 'text to columns' (xp or 2000)

    Well, being lazy, I ... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •