Jeremy said:
Does anyone know if there is a command in Excel to parse
data. I want to execute the "Text to Columns" command in
the Data menu automatically. So that if I type text in a
cell, it will automatically just parse the data into
separate cells without me having to do anything.
Automatically do Text to Columns:-
I am not sure whether you really mean this, but the following will parse out
any text entered into the worksheet
At the moment, I have set it to only do this if the cell you have entered
text into is in column A or rows 4 to 5, but you can easily change that.
Enter it in the relevant sheet's VBA code page
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
'Lines marked *** below are used to restrict the range that this operates
over.
'To use them change the line below to show the actual range you want to
parse over
'To use the entire workbook, just delete those lines
Set MyRangeToParse = Union(Range("A:A"), Range("4:5")) '***
For Each rng In Target
Set insect = Intersect(rng, MyRangeToParse) '***
If insect Is Nothing Then GoTo Skipit '***
If Application.WorksheetFunction.IsText(rng.Address) = True Then
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False
'This assumes text is space-delimited.
End If
Skipit:
Next
End Sub
This can probably also be done using some rather complex formulae, but I
think that would be much more complicated
HTH
Geoff
