Forum Discussion
Need help with formula
I'm trying to take a three digit number i.e. 123 and split it into three pairs; a front pair, a back pair and the outer two numbers form the three pair i.e. 12, 23, 13. I'm having trouble with formula. I tried to copy and past Google Sheets formula
=join(", ",{left(C3,2)&"x","x"&right(C3,2),"x"&left(C3,1)&right(C3,1)}) in Microsoft Excel but I'm getting an error. So what would be the correct formula?
Hi,
You got that error because there is no function in Excel called: JOIN.
Replace this:
=join(", ",{left(C3,2)&"x","x"&right(C3,2),"x"&left(C3,1)&right(C3,1)})With this:
=LEFT(C3,2)&"x,"&" x"&RIGHT(C3,2)&", x"&LEFT(C3,1)&RIGHT(C3,1)
Or this:
=LEFT(C3,2)&", "&RIGHT(C3,2)&", "&LEFT(C3,1)&RIGHT(C3,1)
8 Replies
Hi,
You got that error because there is no function in Excel called: JOIN.
Replace this:
=join(", ",{left(C3,2)&"x","x"&right(C3,2),"x"&left(C3,1)&right(C3,1)})With this:
=LEFT(C3,2)&"x,"&" x"&RIGHT(C3,2)&", x"&LEFT(C3,1)&RIGHT(C3,1)
Or this:
=LEFT(C3,2)&", "&RIGHT(C3,2)&", "&LEFT(C3,1)&RIGHT(C3,1)
If I have a three digit number i.e. 098 that begins with number 0 how do I get Excel to display it? The formula works but its displaying the pairs without number zero i.e. 98x, x98, x98
You may apply custom format to your cells to display leading zero(es)
- Thanks for helping me with formula. It works and giving alternative formula
Hi Alana,
Google Sheets formula not necessary work in Excel, these are different products. Your formula in Excel looks like
=LEFT(C3,2) & "," & RIGHT(C3,2) & "," & LEFT(C3,1) & RIGHT(C3,1)
- Thanks for your help also Sergei
