Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# grouping digits in differents columns

Posted on 2007-03-26
Medium Priority
306 Views
I have a file codebars.txt this file contains numbers in one row, I need put in differents rows groups of 13 digists.
Example
A1: 9788478290413978840309217497899780740399789681909284
I need cut in strings , 13 digits for columns
a1: 9788478290413
b1:9788403092174
c1:9789978074039
d1:9789681909284

The function LEFT only obtain the first 13 digits
Thanks
0
Question by:eccd
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2
• 2
• +3

LVL 75

Expert Comment

ID: 18797967
You could use the Split() function ... let me dig up an example.

mx
0

LVL 81

Expert Comment

ID: 18797994
Use MID function:

=MID(A1,1,13)
=MID(A1,14,13)
=MID(A1,27,13)
=MID(A1,40,13)

Kevin
0

LVL 81

Expert Comment

ID: 18798001
Import that data into column A and then run this macro with the worksheet active:

Public Sub SplitData()

Dim Cell As Range

For Each Cell In Intersect(UsedRange, [A:A]).Cells
Cell.Offset(0, 1).Resize(1, 4).NumberFormat = "@"
Cell.Offset(0, 1) = Mid(Cell, 1, 13)
Cell.Offset(0, 2) = Mid(Cell, 14, 13)
Cell.Offset(0, 3) = Mid(Cell, 27, 13)
Cell.Offset(0, 4) = Mid(Cell, 40, 13)
Next Cell
[A:A].Delete

End Sub

Kevin
0

LVL 50

Expert Comment

ID: 18799393
which database system are you using?

you would normally use the SUBSTRING function (or SUBSTR in some dialects)

select
substring(yourcolumn,1,13) as part1,
substring(yourcolumn,14,13) as part2,
substring(yourcolumn,27,13) as part3,
substring(yourcolumn,40,13) as part4

0

Author Comment

ID: 18803026
Zorvek.
runtime error 424 object required

Lowfatspread: this is a file in MSexcel
0

LVL 81

Expert Comment

ID: 18803047
Public Sub SplitData()

Dim Cell As Range

For Each Cell In Intersect(ActiveSheet.UsedRange, ActiveSheet.[A:A]).Cells
Cell.Offset(0, 1).Resize(1, 4).NumberFormat = "@"
Cell.Offset(0, 1) = Mid(Cell, 1, 13)
Cell.Offset(0, 2) = Mid(Cell, 14, 13)
Cell.Offset(0, 3) = Mid(Cell, 27, 13)
Cell.Offset(0, 4) = Mid(Cell, 40, 13)
Next Cell
ActiveSheet.[A:A].Delete

End Sub

Kevin
0

LVL 75

Expert Comment

ID: 18803063
I'm still working on that Join example, lol. No ... just kidding ... you guys are on it :-)

mx
0

Author Comment

ID: 18803942
Thanks Zorvek

the perfect solution is
=mid(A\$1,b1...bn,13)
but the problem is that the copdebars.txt is very very long, and the excel have problems with it.
Maybe some scrit in vbs or php?
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 18804014
That's what this does:

Public Sub SplitData()

Dim Cell As Range

For Each Cell In Intersect(ActiveSheet.UsedRange, ActiveSheet.[A:A]).Cells
Cell.Offset(0, 1).Resize(1, 4).NumberFormat = "@"
Cell.Offset(0, 1) = Mid(Cell, 1, 13)
Cell.Offset(0, 2) = Mid(Cell, 14, 13)
Cell.Offset(0, 3) = Mid(Cell, 27, 13)
Cell.Offset(0, 4) = Mid(Cell, 40, 13)
Next Cell
ActiveSheet.[A:A].Delete

End Sub

Kevin
0

LVL 7

Assisted Solution

SamIDRC earned 500 total points
ID: 19712043
Hi eccd,

If your data from "codebars.txt" is imported into the spreadsheet in column A, then the following formula could split this into 13-character chunks.

B1: =MID(\$A1,(COLUMN()-2)*13+1,13)
(copy/paste across C1,D1, etc, and down the rows)

Another approach is to use the Data | Get External Data | Import Text File menu, and manually select the 13-character boundaries and "text" for each column.  Save to a "new sheet" and save the file.  This should save the "Query" so the next time "codebars.txt" changes, a simple right-click "Refresh Data" will reimport it into the same places.

Good luck,

/Sam M.
0

LVL 1

Expert Comment

ID: 21177272
Forced accept.

Computer101
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance andâ€¦
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
###### Suggested Courses
Course of the Month9 days, left to enroll