+ Reply to Thread
Results 1 to 5 of 5

Replace empty values from CONCATENA formula

  1. #1
    Registered User
    Join Date
    10-20-2019
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    2

    Replace empty values from CONCATENA formula

    Hi all,

    I'm racking my brains to find I way to hide extra underscore "__" in a concatenate formula if one optional field left empty
    2020_02-Jan_Client1_Finance_Cheese__ABCD_PI-123123

    I managed to do with the formula below, however in different cells. I would like to add everything in the same cell.
    =IF(SEARCH("__",C25),SUBSTITUTE(C25,"__","_"),"")

    Also, with the formula above, if there's no "__" in the result, it returns #VALUE!

    Does anyone have any idea how can I do that in one formula?

    Thank you
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Replace empty values from CONCATENA formula

    What about

    =IFERROR(SUBSTITUTE(CONCATENATE($D12,"_",$E12,"-",$F12,"_",G12,"_",$H12,"_","PI"&"-"&$I12,"_",$J12,"_",$K12,"_",$L12),"__","_"),CONCATENATE($D12,"_",$E12,"-",$F12,"_",G12,"_",$H12,"_","PI"&"-"&$I12,"_",$J12,"_",$K12,"_",$L12))

    HTH,

    Francesco
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Replace empty values from CONCATENA formula

    Try this...
    =SUBSTITUTE(TRIM(D12&"_"&E12&"_"&F12&"_"&G12&"_"&H12&"_"&I12&"_"&J12&"_"&K12&"_"&L12),"__","_")
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-20-2019
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    2

    Re: Replace empty values from CONCATENA formula

    Awesome, it worked! Thank you!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Replace empty values from CONCATENA formula

    Quote Originally Posted by glausantos View Post
    Awesome, it worked! Thank you!
    which 1 worked for you?

+ 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. Replies: 11
    Last Post: 10-11-2019, 12:47 PM
  2. Replace Empty Text/Space in all Empty Cells
    By bhenlee in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-04-2018, 10:07 PM
  3. [SOLVED] Replace Sum Formula with Values Being Summed
    By uberathlete in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-21-2016, 09:55 AM
  4. [SOLVED] Formula to replace values if another exists
    By RaydenUK in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-15-2014, 10:38 PM
  5. Replace formula values with a blank
    By 4joey1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2010, 11:21 PM
  6. automate replace values in formula
    By galiant in forum Excel General
    Replies: 2
    Last Post: 07-10-2006, 03:29 AM
  7. [SOLVED] Can you Find and Replace values with in a formula?
    By rascall in forum Excel General
    Replies: 2
    Last Post: 06-22-2005, 07:05 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