How to capitalize the first letter in a cell in Excel

If you’d like to capitalize all letters in a cell, it’s easy – just use the following formula (assuming cell A1 has the text you want to capitalize).

=PROPER(A1)

But what if you just want to capitalize the first word in the cell “A1”? For example, you have the following  text in a cell “A1” and you’d like to capitalize just the first letter:

How to have the First Letter of a sentence Capitalize

To capitalize “How” in cell “A1”, use one of the following formula:

=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))

or

=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

The result:

How to have the first letter of a sentence capitalize

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

Check if URL exists, is so then return true

Hi everyone, I'm hoping someone can help with this function. it will check if a URL exists. If it does, would like it to return true and if it does not, then false.

Function Test_URLExists()
  Dim url As String
  
  url = "http://www.puresourcecode.com/"
  MsgBox url, vbInformation, URLExists(url)
  
  url = "http://www.puresourcecode.com/showthread.apx?t=1"
  MsgBox url, vbInformation, URLExists(url)
End Function

Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
    
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
    
    Exit Function
EndNow:
End Function

Happy coding!

Get the URL from an Excel Hyperlink

If you paste hyperlinks as values, what you're left with is the "Friendly Name." That's the text you can see in the cell with the hyperlink, like "Sales Report" in the screen shot on left.

Instead of the Friendly Name, you might want to extract the hyperlink's location. There's no built-in function for that in Excel, but you can create your own, with a bit of VBA.

Copy the HLink code, shown below, into a regular module in your workbook.

Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Then, you can use the HLink function in that workbook, just like any other Excel function. For example, to find the address for a hyperlink in cell B3, use this formula: =HLink(B3)

To extract the Friendly Name, use a simple link to the cell: =B3

Delete Blank Rows in Excel

Blank rows or Blank cells is a problem we all inherit one time or another. This is very common when you try to import data from somewhere else (like a text file or a CSV file). Today we will learn a very simple trick to delete blank rows from excel spreadsheets.

• Select your data
• Press F5
  This opens “Go to” dialog in Excel. Now hit on that “select” button.
• From “select special” screen, select “Blanks” (shown aside)
  Now, all the blank cells will be selected.
• Just press CTRL and Minus sign (-)
• Select “shift cells up” or “entire row” as needed.

That is all. Now you have successfully removed blank rows.

If you are looking for keyboard short-cut for this, here it is. Press them in the same order once you select the cells.

• F5 ALT+s k Enter CTRL+ – u Enter