Solved: TextToColumns malfunction

[kikNScreming]

I am using excel 2003. I am having trouble with the TextToColumns function.

I am trying to parse a string delimited by semicolons (;). It also contains spaces. When I code

Selection.TextToColumns ConsecutiveDelimiter:=True, _
Space:=False, _
Semicolon:=True _

it breaks the string on BOTH semicolons and spaces. When I paste the string in a text file and do this process manually, I am able to uncheck spaces and it works properly, breaking the string on ONLY semicolons.

Am I coding something incorrectly? Is this a known bug?

[XLGibbs]

is it data semicolon space data semicolon space?

You may be able to say it has both delimeters and treat consecutive delimiters as one to avoid the issue.

[Zack Barresse]

Can you post exactly what you are trying to do? We may be able to use the Split function..

[kikNScreming]

No. It is usually a windows path like c:\program files\...; c:\windows\system32...

[Zack Barresse]

Can you post exactly what you are trying to do? We may be able to use the Split function..

[XLGibbs]

No. It is usually a windows path like c:\program files\...; c:\windows\system32...

Sounds like you want to extract file names from this...
or you want to separate based on semicolon only to move data to get the full path in a standalone cell. If the ; occurs between each string, the SPLIT function would likely work nicely.

[Zack Barresse]

..the SPLIT function would likely work nicely.
Couldn't have put it better myself. (crunch, crack, pop.. :D )

[XLGibbs]

Can you post exactly what you are trying to do? We may be able to use the Split function..

I do believe you did. :clap:

[Norie]

Have you performed any other Text to Columns before you run this code?

As far as I can see Excel 'remembers' the last Text to Columns and uses
it the next time you open a text file.

[kikNScreming]

Yes, it works, sort of. It seems to have a limit of 1024 characters -- or is that an implied limit in the output array? ( Dim strArrResult() As String )

Dim strFrom As String
Dim strTo As String
Dim intToRow, intToCol As Integer
Dim strDelim As String
Dim intFrom As Integer
Dim intFoundAt As Integer
Dim strArrResult() As String
Dim idxStr As Integer
Dim intMaxStr As Integer

strFrom = refToParse.Text
strDelim = txtDelim.Text
strTo = refDest.Text
Unload frmParseString

strArrResult = Split(Range(strFrom).Text, strDelim, -1, vbTextCompare)
intMaxStr = UBound(strArrResult)

Application.Goto Range(strTo)
intToRow = ActiveCell.Row
intToCol = ActiveCell.Column
For idxStr = 0 To intMaxStr
Cells(intToRow, intToCol + idxStr).Value = strArrResult(idxStr)
Next idxStr

[XLGibbs]

1024 is the character limit for 1 cell (as a formula) for text it is 255. There may be a tandem issue within the limits of a string array...but I didn't think so...

[kikNScreming]

That's interesting, because the SOURCE cell (just text, not a formula) is 1,265 characters long.

[XLGibbs]

That is interesting. A formula can only contain 1024 characters, perhaps the limit is bypassed on text imports. I think the limit may only apply to to text for display and printing purposes (the 255) perhaps.

I will have to get back to this one...kind of tied up at the moment

[kikNScreming]

That's ok, you got me far enough to fiddle around with it. I may have to get funky with a workaround but I will get a solution. Thanks for being generous with your time.

[XLGibbs]

Well, just please remember to mark the thread as Solved if you are done with the issue! Thanks

[Zack Barresse]

There are workarounds for the cell limit, so they are not absolute.

[kikNScreming]

I finished my workarounds. The limits are 1024 characters in any Excel 2003 vba string function, but Excel string functions did not have a limit (that I encountered, anyway). So I had to use Excel formulas in a couple of places. Here is the workaround:

'caculate the length of the string
Range("_len").Formula = "=LEN(" & strFrom & ")"
intMaxStr = Range("_len").Value

'get our starting point address
Application.Goto Range(strTo)
intToRow = ActiveCell.Row
intToCol = ActiveCell.Column

idxFrom = 1
idxWord = 0
For idxStr = 1 To intMaxStr
If Range(strFrom).Characters(idxStr, 1).Text = strDelim Then
idxFoundAt = idxStr
'need to use an Excel formula for this because vb mid ftn has a
'1024 char limit! Just like vb split function.
Range("_mid").Formula = "=MID(" & strFrom & ", " & _
idxFrom & ", " & idxFoundAt - idxFrom & ")"
Cells(intToRow, intToCol + idxWord).Value = _
Range("_mid").Text
idxWord = idxWord + 1
idxFrom = idxFoundAt + 1
End If
Next idxStr