+ Reply to Thread
Results 1 to 2 of 2

Keep certain number from being reduce to a double digit

  1. #1
    Forum Contributor
    Join Date
    08-15-2014
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2021
    Posts
    105

    Keep certain number from being reduce to a double digit

    I am using the formula

    =SUMPRODUCT(1*MID(B18,ROW(INDIRECT("1:"&LEN(B18))),1))

    to add all the numbers in the number together a single number

    In column B I put 1975 in column C I put the above formula and in column D I also put the above formula

    I am try to get a 1 digit number out of any number. How I have it set up is working perfect

    But there are a few numbers I do not want a 1 digit for I want to keep them at 2 digits

    the numbers 11, 22 and 33

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,781

    Re: Keep certain number from being reduce to a double digit

    Try

    =IF(OR(B18={11,22,33}),B18,SUMPRODUCT(1*MID(B18,ROW(INDIRECT("1:"&LEN(B18))),1)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Color a single digit in a mult-digit number cell
    By Phyllis in forum Excel General
    Replies: 8
    Last Post: 09-30-2022, 04:22 PM
  2. [SOLVED] Formatting to make 2 digit number show up at 3 digit number.
    By cadamhill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2012, 06:58 PM
  3. Replies: 4
    Last Post: 09-12-2011, 12:00 PM
  4. [SOLVED] how to identify only 2 digit permuted number from list of 3 digit number
    By bigcrap in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-08-2011, 03:06 PM
  5. [SOLVED] how to identify only 3 digit permuted number from list of 4 digit number
    By bigcrap in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 05-10-2011, 11:30 AM
  6. Replies: 2
    Last Post: 06-17-2010, 08:36 PM
  7. Double Digit number 03 vs 3 with formula
    By RobP614 in forum Excel General
    Replies: 2
    Last Post: 07-13-2009, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1