turn on suggestions

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

Showing results for

- 260K Members
- 8,152 Online
- 61.6K 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

Formula or function for IF statement based on cell color

Laurie McDowell
in
Excel
on
06-15-2017
42.5K
Views

1 Likes

10 Replies

Power Query - Combine Data from Folder with Relative Paths

Justin Schmidt
in
BI and Data Analysis
on
10-24-2017
12.1K
Views

0 Likes

2 Replies

Compile error: Method or data member not found

kevin whiteside
in
Excel
on
06-28-2018
1,636
Views

0 Likes

3 Replies

Copy Data to Other Sheets' Columns Based on Criteria

Rob Nunley
in
Excel
on
09-22-2017
6,049
Views

0 Likes

7 Replies

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

Joe Gray
in
Excel
on
05-30-2017
18.5K
Views

0 Likes

23 Replies

sum by color when colors are set by conditional formatting

matt nipper
in
Excel
on
12-20-2016
50K
Views

0 Likes

148 Replies

Share

Popular

Learning Resources

Programs

Values

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