+ Reply to Thread
Results 1 to 5 of 5

=CONCATENATE question regarding referencing ROW

  1. #1
    Registered User
    Join Date
    06-13-2022
    Location
    Texas
    MS-Off Ver
    Office 365
    Posts
    6

    Question =CONCATENATE question regarding referencing ROW

    Hello all,

    I have several sheets I need to auto number with a specific prefix and number format.

    I uploaded my test sheet to show you what I am trying to do

    Using this formula: =CONCATENATE($B$1,ROW(B1)) I am able to use a cell as a prefix which is fine, but rather than it just numbering 1,2,3, etc, I need to have it in a specific number format such as:

    TC_UA_001
    TC_UA_002
    TC_UA_003
    TC_UA_004
    TC_UA_005
    TC_UA_006
    TC_UA_007
    TC_UA_008
    TC_UA_009
    TC_UA_010
    .....
    TC_UA_100 and so on

    How can I achieve this please?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: =CONCATENATE question regarding referencing ROW

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

  3. #3
    Registered User
    Join Date
    06-13-2022
    Location
    Texas
    MS-Off Ver
    Office 365
    Posts
    6

    Re: =CONCATENATE question regarding referencing ROW

    Quote Originally Posted by Fluff13 View Post
    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That worked perfect!, thank you

    Could you please link me to a document that explains how this works? I searched all over for different uses of the function, CONCATENATE and have never come across this?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: =CONCATENATE question regarding referencing ROW

    Glad to help & thanks for the feedback.

    You just need to use the Excel help for the Text function.

  5. #5
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: =CONCATENATE question regarding referencing ROW

    You don't actually need CONCATENATE here at all; you could just use:

    =$B$1 & TEXT(ROW(B1),"000")

    The TEXT function lets you define the format of the output.

+ 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. concatenate question
    By frustrated in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2015, 07:32 PM
  2. [SOLVED] Concatenate cells referencing another tab - How to get rid of 0's in resulting string
    By Calder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2014, 04:08 PM
  3. [SOLVED] Concatenate question
    By cmax979 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2014, 11:21 AM
  4. Concatenate and referencing a blank cell
    By hexr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2013, 12:47 AM
  5. Replies: 4
    Last Post: 02-23-2012, 04:21 AM
  6. Dynamically referencing file in concatenate
    By coryg in forum Excel General
    Replies: 2
    Last Post: 10-05-2010, 02:12 PM
  7. Creating a concatenate text list by referencing an array
    By nothingbutjeep in forum Excel General
    Replies: 0
    Last Post: 05-30-2006, 04:50 PM

Tags for this Thread

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