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.
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!
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


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