+ Reply to Thread
Results 1 to 4 of 4

Concatenate formula with If and Text function

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    Manchester, England
    MS-Off Ver
    O365
    Posts
    23

    Concatenate formula with If and Text function

    Hi
    Is anyone able to help me with the following please ....

    I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it.
    For example "DFL0715HRS." I have been able to get the following formula to work:
    =CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")


    However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI."
    For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work, it shows "DFL07157R0.":
    =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))

    I think it is trying to continue doing something with the text function and I'm totally confused
    Any help would be great, thanks in advance
    Nic

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Concatenate formula with If and Text function

    try below
    =IF(C10="Rest Day","DFL RDFLEXI","DFL"&TEXT(C10,"HHMM")&"HRS")
    you can use & instead of concatenate
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    Manchester, England
    MS-Off Ver
    O365
    Posts
    23

    Re: Concatenate formula with If and Text function

    Thanks Hemesh that works a treat

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Concatenate formula with If and Text function

    you are welcome and thanks for the feedback.

+ 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. Using the concatenate function with complicated text
    By moty99 in forum Excel General
    Replies: 2
    Last Post: 10-18-2010, 10:18 AM
  2. Concatenate function to join some text strings
    By FRJ1949(Frank) in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2010, 03:30 PM
  3. Preserving text format/style - concatenate function
    By michaelc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2006, 12:51 PM
  4. Using Concatenate function to generate text in Text Box
    By Mary S. in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-14-2005, 05:00 PM
  5. [SOLVED] function:How to Concatenate with a text & number
    By Damon Longworth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM

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