Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?
Hello Jtakw
I understand how the command of MID works, but I’m trying to figure out SUBSTITUTE and REPT, especially how they work together here. I played with your formula by changing the numbers, and found it still seemed to work as long as I kept the same relationship between the ‘30’ and ‘29’.
1 ) Is there any reason you picked ‘30’ rather than some other number?
2 ) If there were more than 15 characters, how would that change your formula? E.g. 20 or 30 characters.
TotallyConfused
Sorry didn't get back to you sooner, had a busy weekend.
The SUBSTITUTE was used to replace the commas within the text string, using the REPT function, with 30 " " (spaces).
So now we have 30 spaces separating each substring, using the MID function, we can now extract the assigned nth element substring.
As far as why I used 30 rather than any other number, there's no definitive answer...each situation is different, so that number will be different for different types of text strings we're working on.
Here, I chose 30 because we're working with numbers, and it's reasonable to assume OP wanted the extracted numbers converted to Real Numbers (not Text, that's where the +0 at the end of the formula does, and also why we removed it when you were using the formula for TEXT rather than numbers), and since Excel can only do Math up to 15 digits, I doubled up to give myself some "cushion", so I chose 30.
Since OP's data are student test scores, it is reasonable to assume the numbers will only be anywhere from 1 to 3 digits, so in theory, 6 would work (but Not 3 due to the possible variances between 1 to 3 digits).
But this number will Also depend on how "Large" the original text string is, meaning it's Total length.
For instance, if the length of the original text string is in the Hundreds or even Thousands, then 30 may Not be sufficient to handle the extraction, because eventually, the extraction will get "thrown off" due to the varying length of the substrings.
So for instances like this, we should use LEN(A2) rather than a set number, so why not just use LEN(A2) for All instances?, "Speed", for this particular formula, the effect is minimal, but there are extraction formulas where LEN(A2) would be used multiple times, and that would slow down the formula.
So if the total length of the original text string is Not "Large", and we know the substrings are within a certain Size, we choose the number accordingly.
Therefore, if your substrings are 20 to 30 characters long, then double up would be a save number to use, but I would probably just use 99 or 100 at that point.
Hope this somewhat clarifies it for you.
