+ Reply to Thread
Results 1 to 4 of 4

Automatically add values in cells up to a set limit, according to varying criteria

  1. #1
    Registered User
    Join Date
    07-06-2017
    Location
    Bucharest, Romania
    MS-Off Ver
    Microsoft Office 2016 version 1706
    Posts
    2

    Automatically add values in cells up to a set limit, according to varying criteria

    Hello! I would greatly appreciate your support with the situation bellow.

    Column A – here I will add different serial numbers of different lengths; the maximum length is 7 characters
    Column B – the maximum length of characters must be 7 for all the cells in this column; I need to add here a formula that does the following:
    1. brings the values from Column A
    2. if the values from Column A have less than 7 characters it automatically adds at the beginning of the string of characters the value "0" (zero), in each cell as many times it is necessary in order to reach the number of 7 characters.

    Column A Column B
    74323 0074323 (2 zeroes must be added in front to reach 7 characters)
    4588996 4588996 (no zeroes added because the series in Col A already has 7 characters)
    5463 0005463 (3 zeroes must be added in front to reach 7 characters)

    I was thinking of a formula similar to the one bellow, only that I do not know what criteria to add to the concatenate function, is possible.

    =IF(LEN(A1)<7, CONCATENATE("0",A1), A1)

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: Automatically add values in cells up to a set limit, according to varying criteria

    Why not just use custom formatting on column A to add the leading zeroes?

    0000000
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    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
    28,178

    Re: Automatically add values in cells up to a set limit, according to varying criteria

    With formula ...

    =IF(LEN(A1)<7, REPT("0",7-LEN(A1))&A1)

    BUT Ali's reply is better.

  4. #4
    Registered User
    Join Date
    07-06-2017
    Location
    Bucharest, Romania
    MS-Off Ver
    Microsoft Office 2016 version 1706
    Posts
    2

    Re: Automatically add values in cells up to a set limit, according to varying criteria

    Thank you very much for your help. In this case John's formula is perfect for what I need.
    I forgot to mention that I can't change the values in column A because for future references I will need both the old series that will be introduced in column A as well as the new ones with the zeroes in front.

+ 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. Macro to special paste values and transpose them to varying cells on another sheet
    By Jenkins87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2016, 02:41 PM
  2. Macro to special paste values and transpose them to varying cells on another sheet
    By Jenkins87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2016, 12:47 PM
  3. Replies: 2
    Last Post: 03-22-2016, 04:58 PM
  4. Automatically hide cells based on two criteria (cell values)
    By mjoc9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2015, 12:56 PM
  5. [SOLVED] Varying validation drop-downs based on varying cell values
    By navarreman1 in forum Excel General
    Replies: 2
    Last Post: 08-08-2013, 08:13 AM
  6. Populate cells with values based on varying criteria
    By bsweet0us in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2010, 03:56 PM
  7. Replies: 4
    Last Post: 06-07-2005, 10:05 AM

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