Forum Discussion

Copper Contributor

Nov 22, 2017

Solved

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 formul...

excel

Excel on Mac

Formulas and Functions

Haytham Amairah

Nov 22, 2017

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)

Haytham Amairah

Silver Contributor

Nov 22, 2017

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)

Alana Weaver

Copper Contributor

Nov 25, 2017

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

SergeiBaklan
MVP
Nov 25, 2017
You may apply custom format to your cells to display leading zero(es)
- Alana Weaver
  Copper Contributor
  Nov 29, 2017
  I created the custom type 000 and it still doesn't recognize the zero only the back pair is displayed
Haytham Amairah
Silver Contributor
Nov 25, 2017
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.