Nov 22 2017 08:28 AM
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?
Nov 22 2017 09:44 AM
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)
Nov 22 2017 09:48 AM - edited Nov 22 2017 09:50 AM
SolutionHi,
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)
Nov 25 2017 10:52 AM
Nov 25 2017 11:19 AM
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
Nov 25 2017 11:39 AM - edited Nov 25 2017 11:40 AM
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:
'098
NOTE: the apostrophe will be hidden after you exit the cell.
Give it a try.
Nov 25 2017 01:43 PM
You may apply custom format to your cells to display leading zero(es)
Nov 29 2017 09:45 AM
I created the custom type 000 and it still doesn't recognize the zero only the back pair is displayed
Nov 22 2017 09:48 AM - edited Nov 22 2017 09:50 AM
SolutionHi,
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)