Entering arrays in a single cell 1

[mgp]

Is it possible to enter a serie of numbers - comma separated - in one cell and then use this "string" as an array in another formula?

For example:

In cell A1 I enter 1,0,0,1,1,1,0

In cell B1 I enter =sum(A1) and the result should have been 4.

I know this doesn't work, but the question is is there some way of manipulating the syntax or entering array formulas that will make it work?

My aim is not to use the sum function - this was just to illustrate the example - I want to make a user defined function to manipulate the values, but I prefer to have the function in a separate cell in order to be able to protect it.

Regards
Mogens

 

[IRstuff]

And why does it all have to be in one cell?

TTFN

 

[bpeirson]

I have used the LEFT, RIGHT & MID functions to do this type of thing.

The formula LEFT(D19,SEARCH("x",D19)-3) will extract the first number from the following text [ 24 5/8" x 18 1/2" ] The result is 24.625.

The formula MID(D19,(SEARCH("x ",D19)+2),LEN(D19)-SEARCH("x ",D19)-2) will extract the second number from the same text.

As long as the text always has the same format including the " x " and the inch units then all I have to do is multiply the 2 formula results to get square inches.

The SEARCH embedded in the formulae will consistently truncate before or after the " x " and the LEN embedded in the MID formula will control how much text to delete to extract the number.

Using the MID rather than the RIGHT formula allows the deletion of the trailing inch unit. For text without trailing units I use the right function.

With some experimentaion you should be able to adapt these to your needs. The MID formula allows you to force the search to start at a predefined character, enter 3 for the command to ingnoer the first 3 letters etc. This may be critical for you since all you numbers are separated with the same character and the SEARCH formula will default to the first target it can find in a line of text.

Good luck, Brad.

 

[m777182]

If you are allways using exactly the same separator, then you can manipulate the embedded values thoug they could have different format, but you should write a subrutine to separate elements of your string array.If you use fixed length format for your values, then bpeirson showed you the way.
m777182

 

[FalsePrecision]

Try this:

Cell[A1]: input your comma-separated values
Example: 14,2217,2,9,12,1,344,45

Cell[A4]=SEARCH(",",A1)
Cell[A5]=SEARCH(",",A$1,A4+1)
then, copy Cell[A5] down to Cell[A10]
Cell[A11]=LEN(A1)+1

Cell[B4]=1*MID(A$1,1,A4-A3-1)
Cell[B5]=1*MID(A$1,A4+1,A5-A4-1)
then, copy Cell[B5] down to Cell[B11]

Cells[B4] thru [B11] should be your values in a vertical "vector". Since Exxcel automatically formats them to text, the 1* portion of the formulae in the Cells [B5]-[B11] converts the values back to numerical.

 

[johnwm]

Just use VBA Split function something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim total As Single
a = Split(ActiveSheet.Cells(1, 1).Value, " ")
For b = LBound(a) To UBound(a)
total = total + Val(a(b))
Next b
ActiveSheet.Cells(1, 2) = total
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[mgp]

Thanks folks

I figured I might have to use a "text separating" formula or function to make it work.

As IRStuff questioned - I don't HAVE to have it all in one cell but I just think it makes a neater spreadsheet in this case as I already have a lot of columns in there. My spreadsheet is for pressure loss calculations and I want to enter the number of bends, tee’s etc in one cell and use the values to calculate the single losses.

I changed the code from johnwm into a worksheet function and it works perfect.

Function SumValues(REF As Range)
Dim total As Single
a = Split(REF.Cells(1, 1).Value, ",")
For b = LBound(a) To UBound(a)
total = total + Val(a(b))
Next b
SumValues = total
End Function


Thanks again for all suggestions

Mogens

 

[IRstuff]

It might be interesting to see if running TextToColumn and the running a summation might be faster.

TTFN