eccd
asked on
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: 97884782904139788403092174 9789978074 0399789681 909284
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
Example
A1: 97884782904139788403092174
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
Use MID function:
=MID(A1,1,13)
=MID(A1,14,13)
=MID(A1,27,13)
=MID(A1,40,13)
Kevin
=MID(A1,1,13)
=MID(A1,14,13)
=MID(A1,27,13)
=MID(A1,40,13)
Kevin
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
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
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
you would normally use the SUBSTRING function (or SUBSTR in some dialects)
select
substring(yourcolumn,1,13)
substring(yourcolumn,14,13
substring(yourcolumn,27,13
substring(yourcolumn,40,13
ASKER
Zorvek.
runtime error 424 object required
Lowfatspread: this is a file in MSexcel
runtime error 424 object required
Lowfatspread: this is a file in MSexcel
Public Sub SplitData()
Dim Cell As Range
For Each Cell In Intersect(ActiveSheet.Used Range, 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
Dim Cell As Range
For Each Cell In Intersect(ActiveSheet.Used
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
I'm still working on that Join example, lol. No ... just kidding ... you guys are on it :-)
mx
mx
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
mx