TextToColumns?

[nolc]

I'm trying to parse an ActiveCell into several columns. Any ideas?

ActiveCell.Columns("A:A").EntireColumn.Select
'
Selection.TextToColumns Destination:=ActiveCell, DataType:= _
xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:=" "

1ST LINE:

" 01-APR-02 05366358A 1004 URAR/Sing 335.00 0824370 V3004587 Clay Routh 4802 twin valley drive Austin TX 78731"


[ This Message was edited by: nolc on 2002-05-06 08:56 ]

[Brian]

METHOD 1:
USE the manual text to columns method {Data > Text to Columns}

METHOD 2:
Here are two subs that can give you an idea how to search for a delimeter and return the text between delemiters. adjust accordingly.

Function CountElements(Txt, Separator) As String
' Counts the number of elements that are separated by a specified separator character in a text string

Dim Txt1, LastCharacter As String
Dim ElementCount As Integer, i As Integer

' Initialize
ElementCount = 0
Txt1 = Txt

' Check that the cell is not blank
If Txt1 = "" Then
CountElements = ""
Exit Function
End If

' Check if there is at least one element
If Txt1 = Separator Then
CountElements = 0
Exit Function
End If

' If the last character is not a separator, then add a separator to the end of the string
LastCharacter = Right(Txt1, 1)
If LastCharacter <> Separator Then Txt1 = Txt1 & Separator

' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
End If
Next i
CountElements = ElementCount
End Function


Function ExtractElement(Txt, n, Separator) As String
' Returns the nth element of a text string, where the elements
' are separated by a specified separator character

Dim Txt1 As String, TempElement As String
Dim ElementCount As Integer, i As Integer

Txt1 = Txt
' If space separator, remove excess spaces
If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)

' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then Txt1 = Txt1 & Separator

' Initialize
ElementCount = 0
TempElement = ""

' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
If ElementCount = n Then
' Found it, so exit
ExtractElement = TempElement
Exit Function
Else
TempElement = ""
End If
Else
TempElement = TempElement & Mid(Txt1, i, 1)
End If
Next i
ExtractElement = ""
End Function

[Brian]

Sorry, that's probably not what you wanted.

Your string worked for me simply by recording a macro when executing data > text To Columns on your data string. It palyed back from VBE fine:

Sub TtoC2()
'
' TtoC2 Macro
' Macro recorded 5/6/2002 by Brian
'
' Keyboard Shortcut: Ctrl+t
'
Selection.TextToColumns Destination:=Range("A20"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1))
End Sub