Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

grouping digits in differents columns

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
eccd
Asked:
eccd
  • 4
  • 2
  • 2
  • +3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You could use the Split() function ... let me dig up an example.

mx
0
 
zorvek (Kevin Jones)ConsultantCommented:
Use MID function:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LowfatspreadCommented:
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
 
eccdAuthor Commented:
Zorvek.
runtime error 424 object required

Lowfatspread: this is a file in MSexcel
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I'm still working on that Join example, lol. No ... just kidding ... you guys are on it :-)

mx
0
 
eccdAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
SamIDRCCommented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now