TextToColumns?

nolc

New Member
Joined
May 1, 2002
Messages
9
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:=" "


<h2>1ST LINE:</h2>
" 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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top