Text to columns help for 6 columns

[scottburbankcpa]

I need to automate the following process and was told to start with getting the data from the PDF file into Excel and then use the Text to Columns to convert the data into a more user friendly format. I need to be able to split the data below into 6 columns. Each text grouping is separated by spaces but with the company names and amounts being variable in length I have not been able to get anything to work on the entire string. I know I am missing something simple but have searched and can't seem to get the formula needed to split the data. I am getting these text files daily and hope to one day have some coding to do this entire process but I am new to this and need help with the less advanced functions first. Thanks in advance for your help. Information breakdown:


Doc ID Company ID Company Name Check Number Date Amount

11110000010 89192108903 ABC Business Solutions 00050001234 10 08 14 1,076.29

11110000011 78085942900 State INDUSTRIAL SERVICE CO 00050002345 10 08 14 97,775.00

11110000012 56167863200 THE HOTEL at State University 00050003456 10 08 14 19,342.56

11110000013 34045898302 Newspaper Company of the North, Inc 00050004567 10 08 14 1,275.02

11110000014 12038919200 Frozen Ice Sales Assn 00050005678 10 08 14 91.81

<tbody>
</tbody>

 

[jim may]

Welcome to the Board Scott.

I read your need and took it on as a project to complete for you. Are you a practicing CPA? Where?

anyway with your sheet laid out as below: BIG ASSUMPTION -- Columns B:M of the populated Col A rows MUST BY BLANK
as they will be populated with the rearrangement of data.

Excel 2012
	A
1	My Data
2	11110000010 89192108903 ABC Business Solutions 00050001234 10 08 14 1,076.29
3	11110000011 78085942900 State INDUSTRIAL SERVICE CO 00050002345 10 08 14 97,775.00
4	11110000012 56167863200 THE HOTEL at State University 00050003456 10 08 14 19,342.56
5	11110000013 34045898302 Newspaper Company of the North, Inc 00050004567 10 08 14 1,275.02
6	11110000014 12038919200 Frozen Ice Sales Assn 00050005678 10 08 14 91.81
Sheet1

Paste this code into a Standard Module and with your sheet here the activesheet run the Macro named Foo.

Write back if q's !!! Jim

Sub Foo()
Dim LR As Long, LC As Long
Dim cnt As Integer, i As Integer
Dim t As Variant
Dim Rng As Range, c As Range
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A2:A" & LR)
For Each c In Rng
t = Split(c)
cnt = UBound(t)
For i = 0 To cnt
Cells(c.Row, i + 2) = t(i)

Next i
LC = Cells(c.Row, Columns.Count).End(xlToLeft).Column
Start = LC - 3
Cells(c.Row, Start) = DateSerial("20" & Cells(c.Row, LC - 1), Cells(c.Row, LC - 3), Cells(c.Row, LC - 2))
Cells(c.Row, LC).Offset(, -2) = Cells(c.Row, LC)
Range(Cells(c.Row, Start + 2), Cells(c.Row, LC)).ClearContents
LC = Cells(c.Row, Columns.Count).End(xlToLeft).Column
Select Case LC

Case Is = 9
Cells(c.Row, 4).Value = Cells(c.Row, 4) & " " & Cells(c.Row, 5) & " " & Cells(c.Row, 6)
Range(Cells(c.Row, 5), Cells(c.Row, 6)).ClearContents
Range(Cells(c.Row, 7), Cells(c.Row, 9)).Cut Destination:=Range(Cells(c.Row, 7), Cells(c.Row, 7)).Offset(0, -2)

Case Is = 10
Cells(c.Row, 4).Value = Cells(c.Row, 4) & " " & Cells(c.Row, 5) & " " & Cells(c.Row, 6) & " " & Cells(c.Row, 7)
Range(Cells(c.Row, 5), Cells(c.Row, 7)).ClearContents
Range(Cells(c.Row, 8), Cells(c.Row, 10)).Cut Destination:=Range(Cells(c.Row, 8), Cells(c.Row, 8)).Offset(0, -3)

Case Is = 11
Cells(c.Row, 4).Value = Cells(c.Row, 4) & " " & Cells(c.Row, 5) & " " & Cells(c.Row, 6) & " " & Cells(c.Row, 7) & " " & Cells(c.Row, 8)
Range(Cells(c.Row, 5), Cells(c.Row, 8)).ClearContents
Range(Cells(c.Row, 9), Cells(c.Row, 11)).Cut Destination:=Range(Cells(c.Row, 9), Cells(c.Row, 9)).Offset(0, -4)

Case Is = 12
Cells(c.Row, 4).Value = Cells(c.Row, 4) & " " & Cells(c.Row, 5) & " " & Cells(c.Row, 6) & " " & Cells(c.Row, 7) & " " & Cells(c.Row, 8) & " " & Cells(c.Row, 9)
Range(Cells(c.Row, 5), Cells(c.Row, 9)).ClearContents
Range(Cells(c.Row, 10), Cells(c.Row, 12)).Cut Destination:=Range(Cells(c.Row, 10), Cells(c.Row, 10)).Offset(0, -5)

Case Is = 13
Cells(c.Row, 4).Value = Cells(c.Row, 4) & " " & Cells(c.Row, 5) & " " & Cells(c.Row, 6) & " " & Cells(c.Row, 7) & " " & Cells(c.Row, 8) & " " & Cells(c.Row, 9) & " " & Cells(c.Row, 10)
Range(Cells(c.Row, 5), Cells(c.Row, 10)).ClearContents
Range(Cells(c.Row, 11), Cells(c.Row, 13)).Cut Destination:=Range(Cells(c.Row, 11), Cells(c.Row, 11)).Offset(0, -6)

End Select
Next c
Range("G:G").Style = "Comma"
Range("B1") = "Col 1"
Range("C1") = "Col 2"
Range("D1") = "Col 3"
Range("E1") = "Col 4"
Range("F1") = "Col 5"
Range("G1") = "Col 6"
Application.ScreenUpdating = True
End Sub

 

[Blade Hunter]

I wondered what you needed the cut code for Jim .

Not sure if this is a "rule" but I always found paste only works for a copy, a cut needed a destination

 

[Blade Hunter]

Formula solution:

Excel 2010
	A	B	C	D	E	F	G
6	11110000010 89192108903 ABC Business Solutions 00050001234 10 08 14 1,076.29	11110000010	89192108903	ABC Business Solutions	00050001234	10 08 14	1,076.29
7	11110000011 78085942900 State INDUSTRIAL SERVICE CO 00050002345 10 08 14 97,775.00	11110000011	78085942900	State INDUSTRIAL SERVICE CO	00050002345	10 08 14	97,775.00
8	11110000012 56167863200 THE HOTEL at State University 00050003456 10 08 14 19,342.56	11110000012	56167863200	THE HOTEL at State University	00050003456	10 08 14	19,342.56
9	11110000013 34045898302 Newspaper Company of the North, Inc 00050004567 10 08 14 1,275.02	11110000013	34045898302	Newspaper Company of the North, Inc	00050004567	10 08 14	1,275.02
10	11110000014 12038919200 Frozen Ice Sales Assn 00050005678 10 08 14 91.81	11110000014	12038919200	Frozen Ice Sales Assn	00050005678	10 08 14	91.81
Sheet1

Cell Formulas
Range	Formula
B6	=TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),(255*1)-254,255))
B7	=TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),(255*1)-254,255))
B8	=TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),(255*1)-254,255))
B9	=TRIM(MID(SUBSTITUTE(A9," ",REPT(" ",255)),(255*1)-254,255))
B10	=TRIM(MID(SUBSTITUTE(A10," ",REPT(" ",255)),(255*1)-254,255))
C6	=TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),(255*2)-254,255))
C7	=TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),(255*2)-254,255))
C8	=TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),(255*2)-254,255))
C9	=TRIM(MID(SUBSTITUTE(A9," ",REPT(" ",255)),(255*2)-254,255))
C10	=TRIM(MID(SUBSTITUTE(A10," ",REPT(" ",255)),(255*2)-254,255))
D6	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,B6,""),C6,""),E6,""),F6,""),G6,""))
D7	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,B7,""),C7,""),E7,""),F7,""),G7,""))
D8	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,B8,""),C8,""),E8,""),F8,""),G8,""))
D9	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A9,B9,""),C9,""),E9,""),F9,""),G9,""))
D10	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A10,B10,""),C10,""),E10,""),F10,""),G10,""))
E6	=LEFT(TRIM(RIGHT(SUBSTITUTE(A6," ",REPT(" ",255)),1280)),11)
E7	=LEFT(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",255)),1280)),11)
E8	=LEFT(TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",255)),1280)),11)
E9	=LEFT(TRIM(RIGHT(SUBSTITUTE(A9," ",REPT(" ",255)),1280)),11)
E10	=LEFT(TRIM(RIGHT(SUBSTITUTE(A10," ",REPT(" ",255)),1280)),11)
F6	=LEFT(TRIM(RIGHT(SUBSTITUTE(A6," ",REPT(" ",255)),1024)),8)
F7	=LEFT(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",255)),1024)),8)
F8	=LEFT(TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",255)),1024)),8)
F9	=LEFT(TRIM(RIGHT(SUBSTITUTE(A9," ",REPT(" ",255)),1024)),8)
F10	=LEFT(TRIM(RIGHT(SUBSTITUTE(A10," ",REPT(" ",255)),1024)),8)
G6	=TRIM(RIGHT(SUBSTITUTE(A6," ",REPT(" ",255)),255))
G7	=TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",255)),255))
G8	=TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",255)),255))
G9	=TRIM(RIGHT(SUBSTITUTE(A9," ",REPT(" ",255)),255))
G10	=TRIM(RIGHT(SUBSTITUTE(A10," ",REPT(" ",255)),255))

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is another formula suggestion to consider.
If the 'Amount' column at the right does not need to be an actual number then you can use the formulas shown in columns B:G.
If you do need the 'Amount' as an actual number, then I suggest considering still using these B:G formulas and add the extra formula in column H. The reason is that it helps keep the earlier column formulas simpler.

All formula copied down. (Blade Hunter: please investigate this option of just showing the first row formulas so your post does not make the thread so long and difficult to navigate by repeating so many similar formulas)

Excel 2010
	A	B	C	D	E	F	G	H
1	Data	Doc ID	Company ID	Company Name	Check Number	Date	Amount (Text)	Amount (Number)
2	11110000010 89192108903 ABC Business Solutions 00050001234 10 08 14 1,076.29	11110000010	89192108903	ABC Business Solutions	00050001234	10 08 14	1,076.29	1,076.29
3	11110000011 78085942900 State INDUSTRIAL SERVICE CO 00050002345 10 08 14 97,775.00	11110000011	78085942900	State INDUSTRIAL SERVICE CO	00050002345	10 08 14	97,775.00	97,775.00
4	11110000012 56167863200 THE HOTEL at State University 00050003456 10 08 14 19,342.56	11110000012	56167863200	THE HOTEL at State University	00050003456	10 08 14	19,342.56	19,342.56
5	11110000013 34045898302 Newspaper Company of the North, Inc 00050004567 10 08 14 1,275.02	11110000013	34045898302	Newspaper Company of the North, Inc	00050004567	10 08 14	1,275.02	1,275.02
6	11110000014 12038919200 Frozen Ice Sales Assn 00050005678 10 08 14 91.81	11110000014	12038919200	Frozen Ice Sales Assn	00050005678	10 08 14	91.81	91.81
Split Text Formulas

Cell Formulas
Range	Formula
B2	=LEFT(A2,11)
C2	=MID(A2,13,11)
D2	=MID(A2,25,LEN(A2)-LEN(G2)-46)
E2	=LEFT(RIGHT(A2,LEN(F2&G2)+13),11)
F2	=LEFT(RIGHT(A2,LEN(G2)+9),8)
G2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
H2	=G2+0

 

[Peter_SSs]

Here is another vba implementation too. Mine returns slightly different results to Jim's.
- Jim's converts the dates to actual dates, mine does not.
- Jim's loses the leading zeros on the Check Numbers (or either of the ID numbers), mine does not.

These are fairly minor issues to change so I'm sure either of us could change those things if it is important to you to have it the other way to what we have.

Test in a copy of your workbook.

I've also assumed data starts in cell A2.

Sub Split_Text()
  Dim a, Bits
  Dim i As Long, UB As Long
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 7)
    a = .Value
    For i = 1 To UBound(a, 1)
      Bits = Split(a(i, 1))
      UB = UBound(Bits)
      a(i, 7) = CDbl(Bits(UB))
      a(i, 6) = Left(Right(a(i, 1), Len(Bits(UB)) + 9), 8)
      a(i, 5) = Bits(UB - 4)
      a(i, 4) = Mid(a(i, 1), 25, Len(a(i, 1)) - Len(Bits(UB)) - 46)
      a(i, 3) = Bits(1)
      a(i, 2) = Bits(0)
    Next i
    .NumberFormat = "#,##0.00"
    .Resize(, 6).NumberFormat = "@"
    .Value = a
  End With
  With Range("B1:G1")
    .Value = Array("Doc ID", "Company ID", "Company Name", "Check Number", "Date", "Amount")
    .EntireColumn.AutoFit
  End With
End Sub

 

[scottburbankcpa]

Thank you all for your quick and helpful posts. I will be able to start testing in the near future and will follow-up.

To answer Jim's Question, I am an active CPA but have sold my practice and work for the State now and I appreciate all the help since this is a project and since I am in my first month of a new job this will help tremendously. Time to play with Excel and I will be back later with questions or success. Thanks again for helping a new guy!