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

1. ## 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. ## 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. ## 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. ## 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>

5. ## 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. ## 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>

#### Posting Permissions

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