Trim Help
 

I need to look at a number and break it down to a basic series of codes. The
codes are from 001 to 100. They are sent to me in a format of
001005003003098050 This represents and needs to be broken down into
individule codes of 1,5,3,3,98,50 There is no set quanties or order it could
range to a single code 005 which equals a 5, to any number of codes combined
as a string. 005005010011012 which equals 5,5,10,11,12 do I have to use VBA
to do this or can I use a formula to extract the individule codes. The only
thing that is set is that each number is 3 digits and the string is always
read from left to right There is never a space nor are quotes or any other
characters used. Any Ideas?

Trim Help
 

You could use the mid function to pare off 3 digits of the string at a
time. Say your string was in cell A1:-
=Mid(A1,1,3), = Mid(A1,4,3), etc

somethinglikeant

Trim Help
 

Try using DATA-TEX TO COLUMNS - Fixed width, and then click on next; at
this point you can insert a break line after every three digits, this will
work even if you only have 001, or a large digit 001005003003098050, you may
want to sort them first by the largest to smallest to find out how many break
lines you need on the column where the data is imported.
I almost forgot, if you need to maintain the number of digits you must
format your cells as text. Starting where the data is imported and then while
doing the use of the TEXT TO COLUMNS functionality.

If you need something more complicated you may need to write some vba. Let
me know.

"Tomkat743" wrote:

I need to look at a number and break it down to a basic series of codes. The
codes are from 001 to 100. They are sent to me in a format of
001005003003098050 This represents and needs to be broken down into
individule codes of 1,5,3,3,98,50 There is no set quanties or order it could
range to a single code 005 which equals a 5, to any number of codes combined
as a string. 005005010011012 which equals 5,5,10,11,12 do I have to use VBA
to do this or can I use a formula to extract the individule codes. The only
thing that is set is that each number is 3 digits and the string is always
read from left to right There is never a space nor are quotes or any other
characters used. Any Ideas?

Trim Help
 

Not sure what you want to do with those codes. If you wanted to store them in
a variable, something like the code below should get you on your way.
Dim strTest As String
Dim arrCodes() As Byte
Dim I As Byte
Dim C As String * 3

' Change this line to point to whatever range or variable contains your
string, or however you want to load in the full text
strTest = "001005003003098050"

I = 1
ReDim arrCodes(0)

Do

C = Mid(strTest, I, 3)

ReDim Preserve arrCodes(UBound(arrCodes) + 1)
arrCodes(UBound(arrCodes)) = CByte(C)

MsgBox arrCodes(UBound(arrCodes))

I = I + 3

Loop Until I = Len(strTest)


--
Hmm...they have the Internet on COMPUTERS now!


"Tomkat743" wrote:

I need to look at a number and break it down to a basic series of codes. The
codes are from 001 to 100. They are sent to me in a format of
001005003003098050 This represents and needs to be broken down into
individule codes of 1,5,3,3,98,50 There is no set quanties or order it could
range to a single code 005 which equals a 5, to any number of codes combined
as a string. 005005010011012 which equals 5,5,10,11,12 do I have to use VBA
to do this or can I use a formula to extract the individule codes. The only
thing that is set is that each number is 3 digits and the string is always
read from left to right There is never a space nor are quotes or any other
characters used. Any Ideas?

Trim Help
 

Thanks, I forgot about the fixed width thing but that actually would work
better than what I was originally thinking. This is a copy of a piece of a
VBA Macro that I use on another project where the codes come in a little
different. I am thinking I could just use this macro to import the column but
am concerned about how I would set the break lines? Could VBA set a fixed
number of lines maybe 10? I don't forsee any job returning more than 10 codes
although it could happen it would not be often. This column is something I
get daily and need to merge into an existing spreadsheet where this column
would be blank. However its a little more complicated in that it must import
the codes by matching the job number column which is probably done by sorting
both spreadsheets by job number before running the import. There is a
probability that additional rows could be on either sheet which may cause a
problem. I guess I would compare the job number columns prior to running the
import. Most of the Macro is written I just have to change that piece to
match this job.
Windows("IMPORT.XLS").Activate
Application.Goto Reference:="R2C11:R200C11"
Selection.Copy
Windows("2DLMONDAY.XLS").Activate
Application.Goto Reference:="R2C9:R200C9"
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.TextToColumns Destination:=Range("I2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True

"Michael" wrote:

Try using DATA-TEX TO COLUMNS - Fixed width, and then click on next; at
this point you can insert a break line after every three digits, this will
work even if you only have 001, or a large digit 001005003003098050, you may
want to sort them first by the largest to smallest to find out how many break
lines you need on the column where the data is imported.
I almost forgot, if you need to maintain the number of digits you must
format your cells as text. Starting where the data is imported and then while
doing the use of the TEXT TO COLUMNS functionality.

If you need something more complicated you may need to write some vba. Let
me know.

"Tomkat743" wrote:

I need to look at a number and break it down to a basic series of codes. The
codes are from 001 to 100. They are sent to me in a format of
001005003003098050 This represents and needs to be broken down into
individule codes of 1,5,3,3,98,50 There is no set quanties or order it could
range to a single code 005 which equals a 5, to any number of codes combined
as a string. 005005010011012 which equals 5,5,10,11,12 do I have to use VBA
to do this or can I use a formula to extract the individule codes. The only
thing that is set is that each number is 3 digits and the string is always
read from left to right There is never a space nor are quotes or any other
characters used. Any Ideas?