I have a file that looks like this:
A1=1x8x2
B1=1 <- LEFT(A1,1)
C1=8 <- MID(A1,FIND("x",A1)+1,1)
D1=2 <- RIGHT(A1,1)
B1=1x6x2
B2=1 <- LET(B1,1)
B3=6 <- MID(B1,FIND("x",B1)+1,1)
B4=2 <- RIGHT(B1,1)
These formulas work fine but I want to use the following formula instead because it is more flexible, for example the formula in C1 won't work on 1x10x2
=TOROW(TEXTSPLIT(A1,,"x"))
But I can't because I get a spill error because TEXTSPLIT is trying to create the array into cells that are filled with data. Is there a way to get around this error and use the new formula?
Can't you move the cells that TEXTSPLIT want to spill into? Or put the formula somewhere else? If not, can you upload a file please? Then we don't have to build a file from scratch based on you textual description.
Given that you already had three formula cells to get the parts from the split, just clear those and enter the formula into the first one.
Incidentally, this:
=TOROW(TEXTSPLIT(A1,,"x"))
can be reduced to:
=TEXTSPLIT(A1,"x")
Here is the file that is an example of what I want to do
Thanks for the file. As Velouria suggested, why don't you remover the cells where you already entered the LEFT, MID and RIGHT formulas (although your example just contains numbers, no formulas).
In the bottom part you seem to want to split the string or the first one if that's the only one. That could be solved with a simple IF.
One observation is that the top part has a blue banded table like format. Did you by any chance try to use the TEXTSPLIT formula inside a structured table? If so, you can't. Dynamic array functions aren't allowed inside such tables.
See attached.
In the example that I provided the rows of information are not in a structured table, they are just different colors.
As for using different formulas, I know that and mentioned that I could do that but that decision to use Textsplit and Torow are that they are more efficient.
Turns out the answer to my problem is very simple and should have been obvious but then obvious isn't always obvious. Here is the solution
=TEXTSPLIT(A1,"x"))
