Using a calculation on data in this format? 8_4_2_0 ??

M

Mark909

I have been given a dataset at work.

It basically outlines information about tubes within different cables.

Each different tube contains up to 12 fibres.

There are different size cables.

The information has been recorded in a column like this:

8_4_2_0
2
4_4
8_4_0_0_2_8_4_2_0_2_2_4_6_2_8_12_12_12_10_1_4_0_8_2
10_12_12
4_4_4_4_12_10_12_12
8_4_12_12_8_6_10_2_2_6_2_10

Is there some way that each set of data in a cell I could subtract each of
the numbers from 12?

The above information would be calculated as:

4_8_10_12
10
8_8
4_8_12_12_10_4_8_10_12_10_10_8_6_10_4_0_0_0_2_10_8_12_4_8
2_0_0_2
8_8_8_8_0_2_0_0
4_8_0_0_4_6_2_10_10_6_10_2


Would it then be possible to take each of these numbers and multiply each of
the numbers by the length in meters in the lenth column to give the total
length of fibres in each of the cables?
 
T

Tom van Stiphout

On Thu, 19 Mar 2009 03:37:03 -0700, Mark909

Sure. Start by putting the numbers in an array for easier
manipulation:
Dim s As String
s = "8_4_2_0"
Dim aryS() As String
aryS = Split(s, "_")

Now you have an array with 4 elements.
Subtract each from 12:
Dim i As Integer
For i = LBound(aryS) To UBound(aryS)
aryS(i) = 12 - aryS(i)
Next i

I leave the rest up to you.

-Tom.
Microsoft Access MVP
 
S

SITOSINTO

Mark909 said:
I have been given a dataset at work.

It basically outlines information about tubes within different cables.

Each different tube contains up to 12 fibres.

There are different size cables.

The information has been recorded in a column like this:

8_4_2_0
2
4_4
8_4_0_0_2_8_4_2_0_2_2_4_6_2_8_12_12_12_10_1_4_0_8_2
10_12_12
4_4_4_4_12_10_12_12
8_4_12_12_8_6_10_2_2_6_2_10

Is there some way that each set of data in a cell I could subtract each of
the numbers from 12?

The above information would be calculated as:

4_8_10_12
10
8_8
4_8_12_12_10_4_8_10_12_10_10_8_6_10_4_0_0_0_2_10_8_12_4_8
2_0_0_2
8_8_8_8_0_2_0_0
4_8_0_0_4_6_2_10_10_6_10_2


Would it then be possible to take each of these numbers and multiply each
of
the numbers by the length in meters in the lenth column to give the total
length of fibres in each of the cables?
Click to expand...
 
Top