Norse Technologies Knowledge Base Free Knowledge Articles

In a Microsoft Excel spreadsheet cell one can use a custom function to effectively grab all test after the final (last) occurrence of a particular character or string. This is accomplished by simply creating a Custom Function and then using that function in a cell formula. Following is the code and instructions for how to create and then implement the function.


Function PullAfterLast(rCell As Range, strLast As String)
PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256)
End Function


1. Highlight the above code, and copy it to the clipboard by pressing CTRL-C, or by right-clicking on the text and on the resulting popup window left-clicking on Copy.
2. Open Microsoft Excel (or proceed to step 2 if you prefer to open Excel by clicking  the desired existing .xls file directly.).
3. Open the workbook you wish to use the function with, or create a new workbook.
4. Open the Visual Basic Editor window by pressing ALT-F11 on the keyboard, or clicking Tools > Macros > Visual Basic Editor
5. Insert a new module by right-clicking on ThisWorkbook > Insert > Module or on the toolbar clicking Insert > Module
6. Paste the clipboard contents (which is the code above this numbered procedure) into the code window by pressing CTRL-V, or by right-clicking in the code window and on the popup menu clicking on Paste, or by clicking the menu toolbar on Edit and clicking on Paste.
7. Close the Visual Basic Editor by presing CTRL-Q, or by clicking File on the menu toolbar and then clicking Close and Return to Microsoft Excel.

The custom user defined function is now available to be used in the formulas of cells. To use it, the following syntax format is used in the cell’s formula:

=PullAfterLast(A1,"yourstring")

Of course, replace yourstring with the appropriate character or string.

This info was combined from ozgrid.com article about how to make custom functions easily, and vertex42.com article where Dave Hawley gave the universe this function’s code, many thanks!

Posted under Excel, Microsoft, Office

The pesky carraige return and new line characters, while providing a way to present text for human viewing, are not always desirable when working in a spreadsheet program like Microsoft Excel or OpenOffice.org.

In Excel, a bit of code provided by Ivan Moala on mrexcel.com, works wonders. Here is the code:


'Created by Chip Pearson
'Cleans up data by removing tabs and carriage returns in worksheet cells.
Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub


Here’s how to use it:

• In Excel click Tools > Macro > Visual Basic Editor (or just hit Alt-F11)
• In the top-left pane under the Project heading, double-click the name of the sheet you want to strip all the newlines and carriage returns from.
• In the window that pops up, paste the code above into the empty box under where you see General and Declarations.
• Run the macro by clicking the “play” button above, or hit F5

Warning: make a backup copy of your Excel workbook before performing the above procedure.

Posted under Excel, Microsoft, Uncategorized

Using conditional formatting one may easily show duplicate values in any chosen range of data. Find all duplicate values, and highlight any values that appear more than once.

=COUNTIF(A:A,A2)>1

This is the meat, and here is how:

• Highlight the cell to work on.
• Click Format > Conditional Formatting
• Set Formula Is
• Enter the formula above, replacing the ranges as follows:
  • (A:A means that it will look inside the entire A range. Replace this with your own range, e.g. B3:B5 or CC:CC
  • ,A2) means to count how many times the value in A2 appears in the designated range. This needs to match the cell you are currently working on.
• Then set the format you want to see when the count of identical values existing in the range is greater than one. This value can be altered to a higher number in order to highlight cells that have more than two duplicates, e.g. change it to be >5 to highlight the cell when at least 5 cells have the same value as the current cell, including this cell itself.
• Copy the formula only to other cells in the range. To do this, copy the current cell, then Paste Special and choose Formulas and paste into the other cells in the range.

An excellent tutorial is at MREXCEL.COM, and thanks to that site for this knowledge and info.

Posted under Excel, Microsoft, Office

Using Office XP, also called Office 2002, could limit you to a 2 gigabyte file size for postoffice .PST and offline storage .OST files.

Microsoft has posted an article addressing this problem, and it recommends updating to the latest Office XP Service Pack. This was performed on a system today, and the successful SP3 update did not remove the 2 gig limitation.

Posted under Microsoft, Office