Here is the trick:
1. Select the character or string you would like to replace
2. Copy (Ctrl+C)
3. Open Find Dialog (Ctrl+F) and open Replace Tab
4. Paste the string to be repalced in the firts textbox (Ctrl+V)
5. Enter in the second textbox Alt+010 which represents a new line with its ASCII equivalent.
6. Click replace and enjoy.
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
Extract url / adress from hyperlink cell in Excel
1. Go to Visual Basic for Applications module by clicking Alt+F11
2. Create new Module by Insert->Module
3. Create a simple function in the newly created module
End Function
2. Create new Module by Insert->Module
3. Create a simple function in the newly created module
Function GetCellUrl(LinkCell as Range)
If LnkCell.Hyperlinks.Count = 0 Then
GetCellUrl = ""
Else
GetCellUrl = LnkCell.Hyperlinks(1).AddressEnd If
End Function
How to sum values in Excel only from visible cells
There is a good article in microsoft support site for this problem and the example works fine.
How to use a VBA Macro to Sum Only Visible Cells
see also how to create user defined functions (UDF)
in Excel
How to use a VBA Macro to Sum Only Visible Cells
see also how to create user defined functions (UDF)
in Excel
Creating User Defined Function in Excel (UDF)
You have to use VBA for making User Defined Function in Excel (UDF).
Click on Developers tab in the ribbon (Excel 2007) then click on Visual Basic or use Alt+F11.
Then insert a new module by Insert->Module and write your function there.
The functions will be inlcuded Insert Function dialog box in "User Defined" category.
To use the functions in another excel documents you must save them in your own add-in by saving the file as add-in file .xla or .xlam for Excel 2007. Then load the add-in by Start->Excel Options->Add-ins->Go.
Click on Developers tab in the ribbon (Excel 2007) then click on Visual Basic or use Alt+F11.
Then insert a new module by Insert->Module and write your function there.
The functions will be inlcuded Insert Function dialog box in "User Defined" category.
To use the functions in another excel documents you must save them in your own add-in by saving the file as add-in file .xla or .xlam for Excel 2007. Then load the add-in by Start->Excel Options->Add-ins->Go.
How to enable VBA on Excel 2007 (Microsoft Office 2007)
The developer's functions of excel and VBA are disabled by default. They can be unlocked using Start->Excel Options window. Check "Show developers tab in the Ribbon" in Popular section. After clicking OK the developers tab is shown at the last place in the ribbon.
Another important thing is the saving xls documetns with recorded macros or inlcuded VBA scripts. You must save the file by Save As dialog box as an Excel Macro-Enabled Workbook with extension .xlsm .
Another important thing is the saving xls documetns with recorded macros or inlcuded VBA scripts. You must save the file by Save As dialog box as an Excel Macro-Enabled Workbook with extension .xlsm .
Subscribe to:
Posts (Atom)