+ Reply to Thread
Results 1 to 6 of 6

After calculating a value with a formula, do not fill in the cell if it's a certain value

Hybrid View

  1. 05-01-2017, 06:03 AM #1
    Mihai9m
    Mihai9m is offline
    Registered User
    Join Date
    05-01-2017
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    5

    After calculating a value with a formula, do not fill in the cell if it's a certain value

    Hello, Great site,


    I am extracting the country code out of email adresses (in column A) when there is no country code given by the person (in column E).

    =IF(E58819="",UPPER(TRIM(RIGHT(SUBSTITUTE(A58819,".",REPT(" ",LEN(A58819))),LEN(A58819)))),E58819)

    The output is the country code (after the last decimal in the email) in capital letters or the country code in column E.

    Because many email adresses end in .com and .net I would like to simply not show them and keep my output field empty. How can I filter them?


    Thanks for checking this out
    Reply With QuoteRegister To Reply
  2. 05-01-2017, 06:08 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: After calculating a value with a formula, do not fill in the cell if it's a certain va

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 05-01-2017, 06:21 AM #3
    Mihai9m
    Mihai9m is offline
    Registered User
    Join Date
    05-01-2017
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    5

    Re: After calculating a value with a formula, do not fill in the cell if it's a certain va

    Thank you for your reply.

    I added the attachment to this message. Thank's alot for looking into this.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 05-01-2017, 06:29 AM #4
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: After calculating a value with a formula, do not fill in the cell if it's a certain va

    Try

    I2
    Formula: copy to clipboard 
    
 
    
 Formula:
 
 
 
	copy to clipboard
  
 
 
 
    
      
    =IF(OR(IF(E2="",UPPER(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)))),E2)={"Com","Net"}),"",IF(E2="",UPPER(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)))),E2))


    drag down!!!
    Reply With QuoteRegister To Reply
  5. 05-01-2017, 06:39 AM #5
    Mihai9m
    Mihai9m is offline
    Registered User
    Join Date
    05-01-2017
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    5

    Re: After calculating a value with a formula, do not fill in the cell if it's a certain va

    Thank you very much Shukla Ankur.

    You are very helpful and insightful.

    Greetings,
    Reply With QuoteRegister To Reply
  6. 05-01-2017, 06:53 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: After calculating a value with a formula, do not fill in the cell if it's a certain va

    Glad it helps your.

    Please add reputation by clicking star * icon left corner on solution post.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for calculating an offset cell sum if cell match text in table
    By mrjackg in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-12-2017, 09:51 PM
  2. VBA to enter formula in cell not calculating...showing formula in cell
    By kmlloyd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2015, 06:42 PM
  3. Need help calculating Fill Rate
    By EequalsWhat in forum Excel General
    Replies: 3
    Last Post: 02-02-2015, 07:45 PM
  4. Auto-fill a cell based on a drop down selection in one cell & a formula in another cell
    By Deena C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-20-2014, 10:57 AM
  5. To fill entire row with fill colour of a particular cell with a formula
    By shailendra chari in forum Hello..Introduce yourself
    Replies: 3
    Last Post: 11-30-2013, 11:18 AM
  6. Formula to Automatically Fill Blank With Next fill cell's data?
    By VMoney in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-22-2013, 05:11 PM
  7. Auto Fill cells in a Column. Fill down from preceeding cell, using a formula.
    By tsa8786 in forum Excel General
    Replies: 1
    Last Post: 09-07-2011, 05:57 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1