By using listi.net you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

listi.net

 


Lesson 3 - Create a string and Tweet

This lesson will teach you the following:
  • Twitter
  • URLs
  • Hyperlink
  • Create a Hyperlink in Excel
  • String
  • Concatenation - combining strings
  • Functions
  • Functions within functions


    Twitter

    To get the most out of this lesson and do a million push-ups you should either already have a twitter account or sign-up for one at Twitter.com.

    It's free, fun and takes a few. If you for 1 million push-ups, I'll follow you. You should also be logged onto your account.


  • What is Twitter?
  • What is tweeting?
  • What is a hashtag #?
  • What does @ mean in twitter?

    We will use Twitter to tell the world of our progress

    If you don't have a twitter account then set one up at Twitter.com

    URLs



    URL is an acronym for a Uniform Resource Locator. It is the address or location of a World Wide Web page. It looks something like http://www.Google.com. This page's URL or Web address is http://Listi.net/showlist2.asp?parent=86653.

    The URL aka Web address  is made up of five parts:
    1. Protocol = http://Listi.net/showlist2.asp?parent=86653 (hyper text transport protocol)
    2. Sub-domain = is optional "www" in http://www.Google.com or http://floorhockey.87sc.com "floorhockey" is the sub-domain.
    3. Domain = Listi.net   http://Listi.net/showlist2.asp?parent=86653
    4. File   = showlist2.asp - http://Listi.net/showlist2.asp?parent=86653
    5. Parameters = ?parent=86653 - http://Listi.net/showlist2.asp?parent=86653&renum=y&hidelinks=y

    Notes:
  • that the first parameter is separated by a ? (question mark) and subsequent are separated by an & (ampersand)
  • The file could have multiple directories /87sc/showlist2.asp
  • The .asp is the which tells the what kind of data Active Server Page (ASP) in this case.


    Hyperlink

    A hyperlink is a clickable location that will go to the link. So the following link will go to Google

    When you position your mouse (hover) over the hyperlink the mouse-pointer will change to a hand and the link may change or some pop-up help will appear.

    Creating a Hyperlink in Excel

    Enter the following to cell:
    K1: https://twitter.com/intent/tweet?text=abaasdfd

    Go back to cell K1 and edit the cell by pressing the F2 .

    When you press Enter the cell will be a hyperlink ().


    The link will only work in cell K1. If you click in L1 or M1 nothing will happen.

    When you Click the cell the following should appear. (Note: you should already be logged onto Twitter)



    Now edit F2 cell K1 and remove the text after the last "=" (equal sign)


    Press Enter



    Enter in cell
    K2: abcde

    the in cell
    K3: =K1&K2

    will appear as:

    and in cell
    K4: =(K3,"Tweet Progress")

    which will display


    in cell K3

    Clicking will open Twitter with the abcde tweet.

    Now change the value in cell K2 and click K4 to see various tweets.


    Build the String

    So I'd like to tweet my progress. I want to say something like

    Goal of #OneMillionPushUps (#1MPU) started on 10/06/2019. To be completed on 3/28/2025. I did 21 today. So far have done 413.  I'm in the 4th of 284 weeks

    I could just type in the above but I want Excel to substitute:
  • Date I started - D1
  • When I'll complete - D285
  • How many I did today - H2 in the 1st week, H3 in second ...
  • Total I've done so far F2 in the 1st week, F3 in the second...
  • How long I've be doing it for Q2 the week I'm in.

    The values from above are already in our spreadsheet from


    String means a string of characters (ex. kkIUEjasldfkke).

    In Excel strings are any characters enclosed in double quotes within a formula (ex. ="here is a string")

    Formulas in Excel start with the "=" (equal sign)

    Combining Strings
    To combine strings is called Concatenation. There are 3 ways
  • Manually just take the data in 2 cells "abc" and "def" and type "abcdef"
  • Concatenate function = =Concatenate("abc","def")
  • Concatenate character = & - (ex.  ="abc"&"def"   --> abcdef

    If you enter data not part of a formula you don't need the quotes.

    I will be using bot the & format and =Concatenate(  ) function, but feel free to use either one.

    So the way to build anything including this string is to start small and build it up. One hard part of this is the special characters you enter have to be exact and it's easy to make a mistake. Okay, so let's build-up to the string
    K5: =concatenate("Goal of ",B2,B1)

    will display

    Goal of 1000000 Pushups

    to fix the formatting of 1000000 we use the =text(    ) function

    K5: ="Goal of "&text(B2,"#,
  • 0")&" "&B1

    Goal of 1,000,000 Push-ups

    Start small and build up like an onion.

    K5: ="Goal of "&text(B2,"#,
  • 0")&" "&B1&". Started on "&D2

    =Concatenate("Goal of ",text(B2,"#,
  • 0")," ",B1,". Started on ",D2)

    Goal of 1,000,000 Push-ups. Started on 43744.

    format the date 43744 which is a number representation of 10/6/2019 via text(D2,"m/d/yyyy")

    The result should be

    =Concatenate("Goal of ",text(B2,"#,
  • 0")," ",B1,". Started on ",text(D2,"m/d/yyyy")




    Dealing with string problems
  • Mismatched " (quotes)
  • missing ( ) parenthesis
  • " inside of a "......"
  • Rebuild the string







    • blog comments powered by Disqus