×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Entering arrays in a single cell

Entering arrays in a single cell

Entering arrays in a single cell

(OP)
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

RE: Entering arrays in a single cell

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

TTFN

RE: Entering arrays in a single cell

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.

RE: Entering arrays in a single cell

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

RE: Entering arrays in a single cell

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.

RE: Entering arrays in a single cell

Just use VBA Split function something like this:

CODE

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: www.essexsteam.co.uk

RE: Entering arrays in a single cell

(OP)
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

RE: Entering arrays in a single cell

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

TTFN

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources