1. In "Replace With:" text box type Alt+0010 (On the keyboard pressing Alt key type 0010 on the keypad). You won't see nothing but the replace will be done.
For Each Cell In
temp = Cell.Text
For i = 1 To Len(temp)
a = Mid(temp, i, 1)
If a = vbCrLf Or a = vbCr Or a = vbLf Then
temp = Left(temp, i - 1) & Right(temp, Len(temp) - i - 1)
Cell.Value = temp
cell reference —
which cells are referenced by this cell: ctl [
which cells reference this cell: ctl ]
column heading as letters instead of numbers - Click Tools, then options, then select the General tab. Unselect the R1C1 Reference Style
crosstab query - see pivot table
date format, custom - let's say you want 2013-03-28. Go into format cells, Select "Custom" instead of "Date". Type yyy-mm-dd
dates off by 4 years when pasted from one spreadsheet to another — see 1900 Date System vs. the 1904 Date System (Tools, Options, Calculations tab, workbook options section, “1904 Date System” checked for one spreadsheet, not checked for another)
Elapsed days - if you have 2 cells with dates in them, simply subtract cell1 from cell2, format as general
emails, converting email addresses to mailto: hyperlinks>
Public Sub ConvertToMailLinks()
Const sPATTERN As String = "?*@?*.?*"
Dim vResult As Variant
Dim rCell As Range
Dim rCheck As Range
If TypeName(Selection) = "Range" Then _
If Selection.Count > 1 Then _
Set rCheck = Selection
If rCheck Is Nothing Then
vResult = MsgBox( _
Prompt:="Search the entire worksheet?", _
Title:="Convert to MailTo: Links")
If vResult = vbYes Then
Set rCheck = ActiveSheet.Cells
Set rCheck = ActiveCell
On Error Resume Next
Set rCheck = rCheck.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rCheck Is Nothing Then
For Each rCell In rCheck
If rCell.Value Like sPATTERN Then _
Address:="mailto:" & rCell.Value, _
external references, find - see links, find external references
"File" menu gone from menu bar — see menu bar does not have the "File" menu
leading zeros — see Zeros, leading: make sure they stay there
links, find external references in a workbook
There is no automatic way to find external references (also called links) that are used in a destination workbook. However, external references use brackets [ ] to enclose the source workbook name when the workbook is closed, for example =SUM([Budget.xls]Annual!C10:C25), so you can use those characters to locate external references.
To find all external references in a destination workbook, you need to look in cells, names, objects (such as a text box or shape), chart titles, and chart data series.
Find external references that are used in cells
- Close all source workbooks, and then open the destination workbook.
- On the Home tab, in the Editing group, click Find & Select, and then click Find.
The Find and Replace dialog box is displayed.
- Click Options.
- In the Find what box, enter [.
- In the Within box, click Workbook.
- In the Look in box, click Formulas.
- Click Find All.
- In the list box that is displayed, look in the Formula column for formulas that contain [.
Tip Click the Formula column header to sort the column, and to group all of the external references together.
- To select the cell with an external reference, select the row in the list box.
Links are also found in names, objects (such as a text box or shape), chart titles, and chart data series. More info.
macros — in the “C:\Program Files\Microsoft Office\OFFICE11\XLSTART” folder the macros reside in “personal.xls”
Hide the file in Excel (Window menu, Hide command).
You keep personal.xls hidden unless you want to add macros to it. Whenever you start Excel, personal.xls opens automatically and runs in the background. If you don't hide the file, Excel opens it and allows you to add data to it.
If everything looks OK but the dang thing still doesn’t work from your shortcut key, look under Help|About MS Excel, you'll see a button called: "Disabled Items...". Check under there to see if it's marked not to open. You can enable it there.
menu bar does not have the "File" menu. Edit, View etc. are all there but not File. How to get it back?
Tools>Customize. On ToolbarTab, make sure WorksheetMenuBar is highlighted and click Reset. "File". Options should be restored to the toolbar.
months elapsed - =12*YEARFRAC(A1,A2)
number — convert to text
if you have the “new” version (Excel 2007, 2010) which ends in “.xlsx”, then you can use one of the following 2 methods:
using the Format > Cells > Number > Text command.
Highlight the column of numbers and use the Data > Text to Columns command. In Page 1 of the wizard, choose the appropriate file type (this will probably be Delimited). In Page 2, remove any column dividers that may have shown up to keep the data in one column. In Page 3, click Text under Column data format to indicate that this column is text.
But if you have the “old” version (Excel 2003 and before) which ends in “.xls”, then you must use this somewhat more cumbersome process:
- Use the TEXT function
- Insert a temporary blank column B.
- In cell B2, enter this formula:
- Fill the formula in B2 down to B3:B100.
- You need to change the formulas to values in order to have them become text. Highlight cells B2:B100.
- Use Ctrl+C to copy, then click Edit > Paste Special > Values > OK.
- The entries in column B will now be text versions of the numbers in column A.
- Copy column B back into column A.
- Delete the temporary column B.
Outlook, can't open Excel files from within (an error has occurred sending the command to the program)
In Excel, File → Options → Advanced tab on the pop-up → General area towards the bottom → uncheck "Ignore other applications that use Dynamic Data Exchange (DDE)" checkbox
parse data you’ve pasted — data, text to columns
- Open the workbook.
- On the File menu, click Save As.
- On the Tools menu of the window that pops up, click General Options.
- In the Password to open or Password to modify box, double-click the asterisks.
- Type the new password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.), and then click OK.
- When prompted, retype the new password, and then click OK.
- Click Save.
- If prompted, click Yes to replace the existing workbook.
pasting dates from one spreadsheet to another results in years being off by 4 years— see dates off by 4 years
pivot table - I usually start by highlighting the cells I want to tabulate - with field headings included up top. Then, from the menu up top, insert tab, pivot table (extreme left). Click where you want put the table (I often keep it in the same data sheet). Then click anywhere in the little template that'll magically appear embedded in your sheet starting with the cell you picked. A "PivotTable Field List" should pop up to the right as a dockable window. You should have fields available at top of that window. Drag whichever field you want to be your row or column labels at the bottom. Then drag the field that holds your detail values into the Values pane. Default is count which is usually worthless for me. Maybe you want sum instead. Click the "Sum of [field name]" pick list in that pane, you'll see a "Value Field Settings..." toward the bottom of that pick list. Click that and a pop-up with ability to change from "count" to "sum".
prepend leading zeros to Zip codes — see Zeros, leading: make sure they stay there
reference to/by cell —
which cells are referenced by this cell: ctl [
which cells reference this cell: ctl ]
references, find external - see links, find external references
split column at first space delimiter only (from here)
If you are only going to split into 2 cells then you could put this formula in the first cell to return the first part:
and this formula in the next cell to return the right-hand remainder
Extract the last substring from a cell - This works, even when there's stuff in the middle (like middle names) (from here):
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))
If you want everything but the last of several space-separated entries (from here)
=LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
which uses a trick of using the "?" as a temporary substitute. Actually, this leaves a trailing space. So use this instead:
=LEFT(E4,FIND("?",SUBSTITUTE(E4," ","?",LEN(E4)-LEN(SUBSTITUTE(E4," ",""))))-1)
to rid of the final space (which you won't be able to see but which can mess you up if you try to pile these results on top of each other.)
If you want everything but the last two of several space-separated entries:
=LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))
text to columns alternative — see split alternative to text to columns
Truncate - =left(a1,1) will return just the first initial, for instance
Unhide top row
- Press F5. Excel displays the Go To dialog box. (Click here to see a related figure.)
- In the Reference field at the bottom of the dialog box, enter the number of the row range that you want to unhide. For instance, if you want to unhide rows 2 through 3, enter 2:3. Likewise, if you want to unhide row 1, enter 1:1.
- Click on OK. The rows you specified are now selected, even though you cannot see it on the screen.
- Choose Row from the Format menu, then choose Unhide. In 2007, Home/Format/Hide & Unhide/Unhide Rows
Write to a spreadsheet programmatically using VBA
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
oExcel = New Excel.Application
oExcel.Visible = True ' <-- *** Optional ***
Dim oRng1 As Excel.Range
Dim oRng2 As Excel.Range
oWB = oExcel.Workbooks.Add
oWS = oWB.Worksheets("Sheet1")
oRng1 = oWS.Range("A1")
oRng2 = oWS.Range("B2:E5")
oRng1.Value = "Hello World"
oWB.SaveAs("C:\Hello World.xls") ' <-- Results saved here.
oWS = Nothing
If Not oWB Is Nothing Then oWB.Close()
oWB = Nothing
oExcel = Nothing
Zeros, leading: make sure they stay there
— Somehow you get in the situation where you’ve stored text info
with leading zeros — like 5-digit zip codes — as numbers so you lose all your
leading zeros. Now, of course, you can make them look like they’re
there by Format Cells / number / and then either Special/Zip Code or Custom /
00000. But if you want to do a mail merge with this data that won’t help a bit.
No, assuming you have a whole column (J) of 500 rows of these wrongly stored
zip codes, one way to do this is to create two adjacent columns. In the first
="0" &J1. Drag that formula down to all the cells in
that column. This is pretty simplistic, assuming you have only 4-digit zip
codes. If you have a mixture of 4 & 5 digit zip codes, then you have to do
=IF(LEN(J1) = 4,"0" &J1, J1). Or,
programmatically for rows 1 through 500:
For i = 1 To 500
If Len(Range("J" & i)) = 4 Then
Range("J" & i) = "'0" & Range("J" & i)
Then highlight the whole 2nd column, copy, and then “paste special” into the 3rd column, taking only values.
Zip codes lose leading zeros — see Zeros, leading: make sure they stay there
4 years difference between dates pasted from one spreadsheet to — see dates off by 4 years