+ Reply to Thread
Results 1 to 6 of 6

If missing/then

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    31

    If missing/then

    Hi Guys,
    I have a list of websites some full and some are missing the beginning eg. "/Interview.html." instead of "http://www.cnn.com/interviews.html" .

    I want to write an if/then statement that says "if cell A1 doesn't start with 'http' Then add 'http://cnn.com' to url and add to current cell, otherwise, copy to current cell"

    Any help is greatly appreciated
    Thank you

  2. #2
    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: If missing/then

    try this...

    =if(left(A1,4)<>"http","http://cnn.com"&A1)
    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

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If missing/then

    Do it in an adjacent column, e.g. in B2:

    =IF(LEFT(A2,4)<>"http","http://cnn.com"&A2,A2)

    then copy down. Then fix the values and delete the original column A.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    05-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: If missing/then

    Quote Originally Posted by FDibbins View Post
    try this...

    =if(left(A1,4)<>"http","http://cnn.com"&A1)
    You are really close. It works for when there is no 'http' (it adds it to the address and puts it in B1) but when there is an 'http' it just inputs "false" into the B1. Anyway it could copy A1 into B1?

    Thanks again

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If missing/then

    Mine works - you just need to add:

    ,A1)

    at the end of FDibbins' formula instead of the )

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    05-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: If missing/then

    =Pete_UK;3283988]Mine works - you just need to add:

    ,A1)

    at the end of FDibbins' formula instead of the )

    Hope this helps.

    Pete
    That did it. Thanks so much!
    Last edited by FDibbins; 06-17-2013 at 03:15 PM. Reason: fixed quotes :)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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