turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 141K Members
- 4,139 Online
- 34.7K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Need help with formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-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?

Labels:

8 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-22-2017 09:48 AM - edited 11-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)

Best Response confirmed by Alana Weaver (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2017 10:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2017 10:53 AM

Thanks for your help also Sergei

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-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*

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2017 11:39 AM - edited 11-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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2017 01:43 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-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

Related Conversations

Keep number from changing after setting cell back to zero

hopr37 hotmail
in
Formulas and Functions
on
03-27-2018
507
Views

0 Likes

16 Replies

Power Query - Combine Data from Folder with Relative Paths

Justin Schmidt
in
Get and Transform Data
on
10-24-2017
2,710
Views

0 Likes

2 Replies

sum by color when colors are set by conditional formatting

matt nipper
in
Macros and VBA
on
12-20-2016
12.8K
Views

0 Likes

48 Replies

Re: Macro to save as PDF with auto filename as cell value

Joe Gray
in
Macros and VBA
on
05-30-2017
4,314
Views

0 Likes

5 Replies

Locking cell value connected to a different workbook

Mary Joyce Tayag
in
Formulas and Functions
on
03-15-2018
152
Views

0 Likes

4 Replies

HELP PLEASE with Macro: delete row if value in row D is below "x" or above "y"

paul.a.t.krueger
in
Macros and VBA
on
03-28-2018
175
Views

0 Likes

12 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft