Need help with formula

Alana Weaver
Occasional Contributor

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?  

8 Replies

Re: Need help with 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)

Re: Need help with formula



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)


Re: Need help with formula

Thanks for helping me with formula. It works and giving alternative formula

Re: Need help with formula

Thanks for your help also Sergei

Re: Need help with formula

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

Re: Need help with formula

Excel automatically removes leading zeros, so you have to convert that number to text in order to display the zero it in the formula.


You can type an apostrophe (') in front of the number, and Excel will treat it as text.


For example:




NOTE: the apostrophe will be hidden after you exit the cell.


Give it a try.


Re: Need help with formula

You may apply custom format to your cells to display leading zero(es)



Re: Need help with formula

I created the custom type 000 and it still doesn't recognize the zero only the back pair is displayed

Related Conversations
Formula or function for IF statement based on cell color
Laurie McDowell in Excel on
10 Replies
Compile error: Method or data member not found
kevin whiteside in Excel on
3 Replies
Copy Data to Other Sheets' Columns Based on Criteria
Rob Nunley in Excel on
7 Replies
Re: Macro to save as PDF with auto filename as cell value
Joe Gray in Excel on
23 Replies
sum by color when colors are set by conditional formatting
matt nipper in Excel on
148 Replies