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 ]
clipboard, prevent from asking if want to save
Application.CutCopyMode = False
code – see vba, Personal Macro Workbook (sometimes called the Personal.xlsb file), vba, open in Visual Studio Code
column heading as letters instead of numbers - Click Tools, then options, then select the General tab. Unselect the R1C1 Reference Style
wsSource.Cells.Copy
wsDestination.Cells.PasteSpecial
Paste:=xlPasteFormats
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)
default location for Excel macros – see Personal Macro Workbook (sometimes called the Personal.xlsb file), macros
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
'Export-Excel' is not recognized
'Export-Excel' is not recognized as a name of a cmdlet, function, script file, or executable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
try
Find-Module - Name ImportExcel | Install-Module
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
focus, return to file from VBA
Application.SendKeys "%{F11}"
formatting, only copy formatting without copying content – see copy formatting only
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 – see Personal Macro Workbook (sometimes called the “Personal.xlsb” file), vba, vba, open in Visual Studio Code
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)
=DATEDIF(start_date, end_date, "m")
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
Personal Macro Workbook (sometimes called the “personal.xls” file) where macros normally reside – see also macros, vba, vba, open in Visual Studio Code
Normally located at: C:\Users\<YourUsername>\AppData\Roaming\Microsoft\Excel\XLSTART
Once you create this, it always comes up whenever you open Excel. That is, 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. This automatic opening feature can become annoying after a while, so you can hide by View → Hide
To unhide again, so you can go to View → Unhide. Look for a workbook named PERSONAL.XLSB or similar. If you find it, that’s your Personal Macro Workbook.
Press Alt + F11 to open the VBA editor. In the Project Explorer, you’ll see a project named VBAProject (PERSONAL.XLSB). Expand it to access the modules and code associated with your Personal Macro Workbook.
Once you have “personal.xls” unhidden, it will pop up alongside any file you open thereafter. To run code against the file you intended to open, begin with focus on that file (not the PERSONAL.XLSB) and open vba, move focus to the macro you want and run (F5 or click the green sideways arror)
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.
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
- Open the Excel workbook containing the hidden file.
- Click on the “View” tab in the ribbon.
- Click on the “Unhide” option.
- select file to unhide.
- 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
vba – see Personal Macro Workbook (sometimes called the Personal.xlsb file), macros
Press Alt + F11 to open the VBA editor.
vba, open in Visual Studio Code – see Personal Macro Workbook (sometimes called the Personal.xlsb file), macros, vba
-
Export the VBA Code:
- Open Excel and access the Personal Macro Workbook (PERSONAL.XLSB) using the VBA editor (Alt + F11).
- In the Project Explorer, find the module or code you want to view.
- Right-click on the module and choose Export File. Save it to a location on your computer.
-
Open in VS Code
- Open VS Code.
- Use the File → Open Folder option to open the folder where you saved the exported VBA file.
-
Install Extensions (Optional):
- To enhance your VBA development experience, consider installing the “VBA” extension for VS Code.
- Search for “VBA” in the Extensions view (Ctrl + Shift + X) and install the one provided by “Dieter Otter”. I couldn’t find one from Dieter, so I tried VBA Pro from Notis Data Analytics. It said to activate, Activated by onLanguage:vba event: 5ms
VBA, return focus to file that called – see focus, return to file from VBA
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
'<-- *** 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