+ Reply to Thread
Results 1 to 7 of 7

concatenate date with number

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    concatenate date with number

    Hi, I need to create a .csv file with the following format:

    yyyy/mm/dd,number

    example:

    2014/01/15,765

    I've tried using the concatenate function but it returns the numerical value of the date, and I can't get it to insert a comma between the date and the number. Tried using the TEXT function too but haven't been successful.

    Any advice?
    Thanks much!
    Last edited by jrtaylor; 07-07-2017 at 08:02 PM.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: concatenate date with number

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

    v A B C
    1 1/15/2014 765 2014/01/15 ,765
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: concatenate date with number

    Thanks AlKey. It looks like there's a space to the left of the comma. Is there one (there shouldn't be) ... or is it just the font on this post? I appreciate your help!

    Oh I'm also testing it, and when a number exceeds 999, excel is putting in a coma (as in 2014/01/15,1,451). Somehow we need to remove that comma from the number. I removed it in column B via formatting, but in column C the , appears again
    Last edited by jrtaylor; 07-07-2017 at 07:39 PM.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: concatenate date with number

    Quote Originally Posted by jrtaylor View Post
    Thanks AlKey. It looks like there's a space to the left of the comma. Is there one (there shouldn't be) ... or is it just the font on this post? I appreciate your help!
    No, there is no space
    7-7-2017.JPG

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: concatenate date with number

    Thanks I was editing my post as you were answering. Can you help with the second issue (a comma in 4+ digit numbers?)

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: concatenate date with number

    Quote Originally Posted by jrtaylor View Post
    Thanks I was editing my post as you were answering. Can you help with the second issue (a comma in 4+ digit numbers?)
    The formula I gave you will enter comma when a number will become 4-digit

  7. #7
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: concatenate date with number

    Thanks, I appreciate your help (I needed to remove the comma but was able to figure out how to do this based on the formula you provided)

+ 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. [SOLVED] Concatenate a date field keeping the date format
    By rrcrossman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2016, 04:28 PM
  2. [SOLVED] Concatenate whole number
    By bmbalamurali in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-16-2014, 10:14 AM
  3. [SOLVED] COncatenate Number and ;
    By Dante_sparda in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-01-2012, 03:58 PM
  4. Concatenate - number zero
    By lynnmc26 in forum Excel General
    Replies: 3
    Last Post: 01-14-2011, 05:03 PM
  5. [SOLVED] How to Concatenate with a text & number
    By Damon Longworth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  6. [SOLVED] [SOLVED] I know how to concatenate ,can one de-concatenate to split date?
    By QUICK BOOKS PROBLEM- in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-26-2005, 01:05 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