Array Formula not working in Office 365 (Mac)

%3CLINGO-SUB%20id%3D%22lingo-sub-120126%22%20slang%3D%22en-US%22%3EArray%20Formula%20not%20working%20in%20Office%20365%20(Mac)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-120126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EDoes%20anyone%20know%20if%20there%20is%20a%20way%20to%20split%20a%20cell%20by%20Capital%20letters%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EFor%20example%2C%20turning%20IceCream%20into%20Ice%20Cream%20(ie.%26nbsp%3BIce%20and%20Cream%20in%20different%20cells).%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20attempted%20this%2C%20but%20I%20cannot%20get%20it%20to%20work.%20Excel%20keeps%20on%20saying%20there%20is%20an%20error%20with%20the%20formula%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3ELets%20assume%20on%20Cell%20A1%20you%20have%20the%20word%20that%20needs%20to%20be%20split.%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EOn%20cell%20B1%20you%20enter%20the%20following%20function%20but%20you%20need%20to%20make%20sure%20that%20you%20use%20CTRL%2BSHIFT%20%2BENTER%20after%20entering%20the%20function%3A%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3E%3DLEFT(A1%2CSMALL(FIND(CHAR(ROW(INDIRECT(%2265%3A90%22)))%2CA1%26amp%3B%22ABCDEFGHIJKLMNOPQRSTUVWXYZ%22)%2C2)-1)%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EThis%20will%20only%20provide%20you%20%22Ice%22%20as%20the%20result%20on%20Cell%20B1.%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EThen%20you%20click%20on%20Cell%20C1%20and%20use%20the%20function%3A%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3E%3DREPLACE(A1%2C1%2CLEN(B1)%2C%22%22)%20%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3Efollowed%20by%20CTRL%20%2BSHIFT%20%2BENTER%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EThis%20should%20give%20you%20the%20result%20as%20%22Cream%22%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAppreciate%20the%20help%20in%20advance.%3CBR%20%2F%3ERegards%2C%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EKP%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-120126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-120150%22%20slang%3D%22en-US%22%3ERe%3A%20Array%20Formula%20not%20working%20in%20Office%20365%20(Mac)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-120150%22%20slang%3D%22en-US%22%3E%3CP%3EBy%20the%20way%2C%20shorter%20version%20of%20second%20formula%20is%3C%2FP%3E%3CPRE%3E%3DSUBSTITUTE(A1%2CB1%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-120138%22%20slang%3D%22en-US%22%3ERe%3A%20Array%20Formula%20not%20working%20in%20Office%20365%20(Mac)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-120138%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20formulas%20work%20(second%20not%20array%20one)%2C%20please%20see%20attached.%20What%20kind%20of%20error%20do%20you%20have%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

Does anyone know if there is a way to split a cell by Capital letters?

For example, turning IceCream into Ice Cream (ie. Ice and Cream in different cells).

I have attempted this, but I cannot get it to work. Excel keeps on saying there is an error with the formula:

Lets assume on Cell A1 you have the word that needs to be split.

On cell B1 you enter the following function but you need to make sure that you use CTRL+SHIFT +ENTER after entering the function:

=LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)

This will only provide you "Ice" as the result on Cell B1.

Then you click on Cell C1 and use the function:

=REPLACE(A1,1,LEN(B1),"")

followed by CTRL +SHIFT +ENTER

This should give you the result as "Cream"

Appreciate the help in advance.
Regards, 

KP

2 Replies

Hi,

 

Both formulas work (second not array one), please see attached. What kind of error do you have?

 

By the way, shorter version of second formula is

=SUBSTITUTE(A1,B1,"")