Carriage returns
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.
2. Sub ClearCRLF()
For Each
Cell In
Selection
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)
End If
Next
Cell.Value = temp
Next
Sub
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 - examples
for “2023-03-28”, go into format cells,
Select “Custom” instead of “Date” and specify yyy-mm-dd
for “03/28 9:13 pm”, use m/d h:mm AM/PM
for “Saturday, Jan 14 2:27 pm”,
use ddd,mmmmm dd h:mm AM/PM
for “Tue Jan 23”, use ddd, mmm 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
elapsed minutes - if you have 2 cells with times in them such that A1 has the earlier time and B1 has the later time, =(B2-B1)*24*60
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?", _
Buttons:=vbYesNo, _
Title:="Convert to MailTo: Links")
If
vResult = vbYes Then
Set
rCheck = ActiveSheet.Cells
Else
Set rCheck = ActiveCell
End If
End If
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 _
ActiveSheet.Hyperlinks.Add _
anchor:=rCell, _
Address:="mailto:" & rCell.Value, _
TextToDisplay:=rCell.Value
Next rCell
End If
End Sub
external references, find - see links, find external references
"File" menu gone from menu bar – see menu bar does not have the "File" menu
floor function - a way to cut off the decimal portion of a number
=FLOOR(3.141596,1) returns 3
see also mod function for remainder when dividing integers, split months into years and months
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.
minutes between times – see elapsed minutes
mod function - Returns the remainder after a number (dividend) is divided by another number (divisor)
example: =MOD(10,3) returns 1
see also related/complementary FLOOR function, split months into years and months
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:
=TEXT(A2,"0")
- 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
Password, change
- Open the workbook.
- On the File menu, click Info.
- Select the Protect Workbook box and choose Encrypt with Password.
- Enter a password in the Password box, and then select OK.
- Confirm the password in the Reenter Password box, and then select OK.
pasting dates from one spreadsheet to another results in years being off by 4 years– see dates off by 4 years
pick list - data tab → Data Tools group → Data Validation icon
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
remainder - see mod function, split months into years and months
split alternative to text to columns – - examples below assume text with multiple spaces and we're splitting on spaces but there are extra, extraneious spaces and we need to get stuff only at the end, etc.
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:
=LEFT(A1,FIND(" ",A1,1)-1)
and this formula in the next cell to return the right-hand remainder
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))
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))
or an alternate (from here) that puts the last chunk of text in column C:
C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
and which has the added bonus of being able to get all the stuff up to that last space into column B:
B1 =LEFT(A1,LEN(A1)-LEN(C1)-1)
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)))
split months into years and months
|
A |
B |
C |
1 |
Total months |
years |
leftover months |
2 |
|
=FLOOR(A3/12,1) |
=MOD(A3,12) |
3 |
137 |
11 |
5 |
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
Sub Main()
Dim
oExcel As Excel.Application
Dim
oWB As Excel.Workbook
Dim
oWS As Excel.Worksheet
oExcel = New Excel.Application
oExcel.Visible = True <
span style='color:green'>' <-- *** 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"
Call oRng1.Copy(Destination:=oRng2)
oWB.SaveAs("C:\Hello World.xls") ' <--
Results saved here.
Cleanup:
oWS = Nothing
If Not oWB Is Nothing Then
oWB.Close()
oWB = Nothing
oExcel.Quit()
oExcel = Nothing
End Sub
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
column, ="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
conditional formatting: =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)
End If
Next 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