+ Reply to Thread
Results 1 to 4 of 4

Formula to concat cells together and then add new components in LEN is not 18

  1. #1
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Formula to concat cells together and then add new components in LEN is not 18

    Hello,

    I need to combine columns to create an 18 digit code.

    Col A

    00001
    00012
    00005
    00001
    00001

    Col B

    4444
    4444
    333
    333
    22

    Col C

    _
    _
    _
    _
    _

    Col D

    AAAAA
    BBBB
    CCC
    DD
    AAAAA

    Col E

    020
    014
    064
    014
    014

    So, currently Col A1+B1+C1+D1+E1 = 000014444_AAAAA020 - length being 18


    And now A4+B4+C4+D4+E4 = 00001333_DD014 HOWEVER length is only 14 which is wrong. The string text needs to read 00001333__DD___014


    So, the formula I need is to make my concat = an 18 character length and the way the string text needs to read is


    Col A 00001 + Col B 4444 OR 333_(1 underscore) OR 22__ (2 underscores) + Col C _ + Col D AAAAA or CCC_ (1 underscore) or DD___ (3 underscores) + Col E 3 digit number.


    So Col A = 5 characters. Col B = 4 characters. Col C = 1 characters. Col D = 5 characters. Col E = 3 characters.


    So, if Col B does not have 4 characters I need an _ to be added to make that number move to 4, and the same with Col D, if the characters are not 5 then I need underscores added at the end of the text to make the text more to 5.



    Any help is massively appreciated.


    Apologies for formatting, tired to explain as best I can
    Last edited by batexcel; 03-13-2019 at 12:22 PM.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Formula to concat cells together and then add new components in LEN is not 18

    Put into F1 (I guess):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and drag it down as needed.
    Should works.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Formula to concat cells together and then add new components in LEN is not 18

    thank you!

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Formula to concat cells together and then add new components in LEN is not 18

    You welcome.
    If you happy with solution, please use thread tools and mark thread as SOLVED.

+ 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. CONCAT/TEXTJOIN on Variable Number of Cells
    By OlYeller21 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-10-2019, 06:29 AM
  2. CONCAT formula for number format
    By Grayc16 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-09-2017, 03:19 PM
  3. Help with formula: Concat All on Excel 2007
    By egburrough in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-29-2016, 04:18 AM
  4. trying to concat 2 cells in one DateTime value
    By acheo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-08-2016, 06:41 PM
  5. Replies: 0
    Last Post: 06-05-2014, 10:45 AM
  6. Combine multiple cells in columns - Concat with variable spacing using VBA Script
    By AMC_CO in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-13-2013, 03:57 PM
  7. [SOLVED] concat cells based on date range
    By DamianWarS in forum Excel General
    Replies: 8
    Last Post: 09-14-2012, 12:39 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