Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: TextToColumns?

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Minneapolis, MN
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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