PDA

View Full Version : Text To Columns VBA



fredlo2010
07-17-2012, 09:26 PM
Hello guys,

I need to do a "text to column" task in Excel.

My data is "-" delimited and contains three of these. So in cell A1 I will have this "23-3-124" I want to import the first and last column; but leave out the middle one.

When I record the macro I get this code:

Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 1)), _
TrailingMinusNumbers:=True

Where is the code which column to import and which to leave out is? Or this is something the macro recorder does not get?

Thanks a lot

Trebor76
07-17-2012, 10:17 PM
Hi fredlo2010,

Try this:


Option Explicit
Sub Macro2()

'http://www.vbaexpress.com/forum/showthread.php?t=43002

Dim varMyItem As Variant
Dim lngMyOffset As Long

For Each varMyItem In Split(Range("A1"), "-")
If lngMyOffset = 0 Then
Range("A1").Offset(0, 1).Value = varMyItem
ElseIf lngMyOffset = 2 Then
Range("A1").Offset(0, lngMyOffset).Value = varMyItem
End If
lngMyOffset = lngMyOffset + 1
Next varMyItem
End Sub

HTH

Robert

Bob Phillips
07-18-2012, 01:35 AM
It is all explained in VBA help. Fieldinfo is an array of arrays, the first element being the column number, the second being how the data is interpreted.

fredlo2010
07-18-2012, 07:10 AM
Thanks a lot for the information guys. Sometimes I forget the Object reference as a point to start. For some reason my help file in excel is sort of limited.

Here is the link I got the information from : http://msdn.microsoft.com/en-us/library/aa299786(v=office.10).aspx

And here is a code I worked out without the constant numbers to make it more readable. ( this is just so other like me out there can grasp the concept)

Range("A1").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(3, xlSkipColumn), _
Array(1, xlTextFormat), Array(2, xlTextFormat), _
Array(4, xlTextFormat)), _
TrailingMinusNumbers:=True

Thanks a lot once more. :)

Bjarte
02-03-2013, 09:20 AM
Hi fredlo2010,

Try this:


Option Explicit
Sub Macro2()


Dim varMyItem As Variant
Dim lngMyOffset As Long

For Each varMyItem In Split(Range("A1"), "-")
If lngMyOffset = 0 Then
Range("A1").Offset(0, 1).Value = varMyItem
ElseIf lngMyOffset = 2 Then
Range("A1").Offset(0, lngMyOffset).Value = varMyItem
End If
lngMyOffset = lngMyOffset + 1
Next varMyItem
End Sub

HTH

Robert

I'm new to VBA, but I got to facing a problem which this little could fix, thank you Robert. But my problem is that I only get to run it on one cell, and not the full range, ( 500 rows). Since I'm new, I've not been able to figure out why. Any Ideas?

Brgds
Bjarte

Trebor76
02-03-2013, 02:31 PM
Hi Bjarte,

Welcome to the forum!!

Try this:


Option Explicit
Sub Macro2()

'http://www.vbaexpress.com/forum/showthread.php?t=43002

Dim varMyItem As Variant
Dim lngMyOffset As Long, _
lngStartRow As Long, _
lngEndRow As Long
Dim strMyCol As String
Dim rngCell As Range

lngStartRow = 2 'Starting row number for the data. Change to suit.
strMyCol = "A" 'Column containing the data. Change to suit.

Application.ScreenUpdating = False

For Each rngCell In Range(strMyCol & lngStartRow & ":" & strMyCol & Cells(Rows.Count, strMyCol).End(xlUp).Row)

lngMyOffset = 0

For Each varMyItem In Split(rngCell.Value, "-")
If lngMyOffset = 0 Then
rngCell.Offset(0, 1).Value = varMyItem
ElseIf lngMyOffset = 2 Then
rngCell.Offset(0, lngMyOffset).Value = varMyItem
End If
lngMyOffset = lngMyOffset + 1
Next varMyItem

Next rngCell

Application.ScreenUpdating = True

MsgBox "Process completed"

End Sub

Regards,

Robert

snb
02-04-2013, 03:55 AM
If column A contains the data that have to be split;
If - is the 'splitting' separator
If each cell contains 4 items, separated by three separators
If the third element is to be discarded,
You can use:

Sub M_snb()
Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "-", Array(Array(1, 2), Array(1, 2), Array(3, 9), Array(4, 2))
End Sub

Bjarte
02-06-2013, 01:07 PM
Hi Robert,

It works like a charm. :bow:

With minor adjustments I was able to do exactly what I wanted, and ended up with this:

Sub Txt2Col1()

Dim varMyItem As Variant
Dim lngMyOffset As Long, _
lngStartRow As Long, _
lngEndRow As Long
Dim strMyCol As String
Dim rngCell As Range

lngStartRow = 2 'Starting row number for the data. Change to suit.
strMyCol = "A" 'Column containing the data. Change to suit.

Application.ScreenUpdating = False

For Each rngCell In Range(strMyCol & lngStartRow & ":" & strMyCol & Cells(Rows.Count, strMyCol).End(xlUp).Row)

lngMyOffset = 0

For Each varMyItem In Split(rngCell.Value, " - ")
If lngMyOffset = 0 Then
rngCell.Offset(0, 1).Value = varMyItem
ElseIf lngMyOffset = 2 Then
rngCell.Offset(0, 2).Value = varMyItem
ElseIf ingMyOffset = 0 Then
rngCell.Offset(0, 3).Value = varMyItem
End If
lngMyOffset = lngMyOffset + 2
Next varMyItem

Next rngCell

Application.ScreenUpdating = True

MsgBox "Process completed"

End Sub

Thanks a lot
Brgds
Bjarte

Trebor76
02-06-2013, 02:35 PM
Thanks for the feedback and I'm glad we were able to provide you with a solution :)

snb
02-07-2013, 04:38 AM
In VBA I'd use:

sub M_snb()
for each cl in columns(1).specialcells(2)
if instr(cl.value,"-") then cl.resize(,3)=split(cl.value,"-")
next
end sub