text to columns macro for irregular spacing of data

M

mmnunn

New Member
Joined
Mar 1, 2010
Messages
17
hello-

i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.

46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285

i would like to automatically run a macro that puts this data in to 4 columns and recognizes when to put a separator after a certain value or number of characters. i have tried the space and tab delimited options and those did not work either.

to give you an idea of where i need columns to go, after every 5, i would like a separator. unfortunately, i just put the 5 there to make it easier to understand, those values will change all of the time and may also be letters in the case of the first column.

any ideas would be greatly appreciated.

here is what the macro looks like as of now.

Sub TextDataToColumns()

FinalRow = Cells(Rows.Count, 11).End(xlUp).Row
Range("K2:K" & FinalRow).TextToColumns Destination:=Range("K2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(35, 1), Array(45, 1)) _
, TrailingMinusNumbers:=True
End Sub

<table style="border-collapse: collapse; width: 44px; height: 168px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt; width: 57pt;" width="76" height="19">

</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td> </tr> </tbody></table>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Would something like this help? Sticking with formulas - your original data in Column A
Excel Workbook
ABCDE
146632hac5Jpmcc2007-ld12a519,340,00531546632hac5Jpmcc2007-ld12a519,340,005315
20738qac5bscms2007-pw17a523,142,005265
361746wcz5msdwc2000-prina53,600,005305
432108hp75bacm2007-2a52,000,005465
546630edf5lbubs2006-c1a51,000,005285
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B1=LEFT(A1,9)
C1=MID(A1,10,9)
D1=MID(A1,19,9)
E1=MID(A1,28,20)
 
Upvote 0
layout as above but
in b1
=LEFT(A1,FIND(" ",A1)-1)
c1
=MID(SUBSTITUTE(A1," ","^",4),LEN(B1)+1,FIND("^",SUBSTITUTE(A1," ","^",4))-(LEN(B1)+1))
d1
=MID(SUBSTITUTE(A1," ","^",5),LEN(B1)+LEN(C1)+2,FIND("^",SUBSTITUTE(A1," ","^",5))-(LEN(B1)+LEN(C1)+2))
e1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),10))
 
Upvote 0
jim-

the problem i have is that the length of the columns will change depending on what deal i am getting data for. three examples would be:

aw666fge_bacm 2005-ldp12 a5_25,000,000_350/s

afgrt56iuj_wbcmt 2003-c31 a6_19,000,000_450/s

awhjg59_bacm 2003-1 a4_8,000,000_560/s


the _ is exactly where i want a column break. however those are not in the data and usually there are lots of spaces separating each column so there is never a set width i can use. also, as you can see in the first and second columns, their length can be a varied number

thank you for responding so quickly. please let me know if i need to clarify anything or if you want a spreadsheet with some of the data.
 
Upvote 0
wel what i posted works also if you have more spaces between things just use trim to get rid of then first
these work with the data you posted (after i removed _ and replaced with " "'s of varying lengths
b1 =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)
c1= =TRIM(MID(SUBSTITUTE(TRIM(A1)," ","^",4),LEN(B1)+1,FIND("^",SUBSTITUTE(TRIM(A1)," ","^",4))-(LEN(B1)+1)))
d1=TRIM(MID(SUBSTITUTE(TRIM(A1)," ","^",5),LEN(B1)+LEN(C1)+2,FIND("^",SUBSTITUTE(TRIM(A1)," ","^",5))-(LEN(B1)+LEN(C1)+2)))
e1=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",20)),10))
 
Upvote 0
If 5 spacing is consistent leading to 6 columns then

1.Text to columns with space delimited & consecutive delimiter.
2.Merge 2,3 and 4 columns.
3.Delete 3 and 4 columns.

Try this on a copy of your data sheet!:

Code: 
Sub Split4()
FinalRow = Cells(Rows.Count, 11).End(xlUp).Row
'split based on space into 6 columns
Range("K2:K" & FinalRow).TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, Space:=True, FieldInfo:= _
    Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
'merge column M and N into L
For i = 2 To FinalRow
    Cells(i, "L") = Cells(i, "L") & " " & Cells(i, "M") & " " & Cells(i, "N")
Next i
Columns("N").Delete 'delete columns M and N
Columns("M").Delete
End Sub
 
Upvote 0
martin- i ran in to a problem with the second and third columns returning errors. those equations are definitely useful though and i thank you for offering your assistance.

drsarao- i tried your macro and that works perfectly. thank you very much for your help.
 
Upvote 0
There is a better way with VBA native Split function.
Shorter, faster, more efficient and easier to understand:
Code: 
Sub Split4b()
FinalRow = Cells(Rows.Count, "K").End(xlUp).Row
'split based on space into array tmp (0 to 5)
For i = 2 To FinalRow
    tmp = Split(Cells(i, "K"))
    Cells(i, "K") = tmp(0)
    Cells(i, "L") = tmp(1) & " " & tmp(2) & " " & tmp(3)
    Cells(i, "M") = tmp(4)
    Cells(i, "N") = tmp(5)
Next i
End Sub
Split() chops the given string at given delimiter (default is space) and returns a zero based array.
The returned array elements can then be fed back to respective columns.
On large dataset this code will be significantly faster.
Even faster implementation will involve reading the whole K data into an array - process in arrays - write back whole K:N columns.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
392
AndyEd
A
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
A
  • Question
Listing values in an array in chronological order
Replies
7
Views
1K
Peter_SSs
Peter_SSs
D
  • Question
need macro help - text extraction from cell with line breaks
Replies
3
Views
689
Fluff
Fluff
O
  • Question
VBA text to columns for a document of inconsistent strings
Replies
2
Views
556
Obzen3
O

Forum statistics

Threads
1,214,827
Messages
6,121,818
Members
449,049
Latest member
cybersurfer5000

Share this page

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