Solved

grouping digits in differents columns

Posted on 2007-03-26
12
300 Views
Last Modified: 2008-03-20
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
Comment
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
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 75
ID: 18797967
You could use the Split() function ... let me dig up an example.

mx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:zorvek (Kevin Jones)
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 50

Expert Comment

by:Lowfatspread
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

by:eccd
ID: 18803026
Zorvek.
runtime error 424 object required

Lowfatspread: this is a file in MSexcel
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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
ID: 18803063
I'm still working on that Join example, lol. No ... just kidding ... you guys are on it :-)

mx
0
 

Author Comment

by:eccd
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

by:
zorvek (Kevin Jones) earned 125 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

by:SamIDRC
SamIDRC earned 125 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

by:Computer101
ID: 21177272
Forced accept.

Computer101
EE Admin
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question