by Allen Wyatt
(last updated August 29, 2015)
Gary has a worksheet that contains unstructured text strings that he needs to parse into component elements. Sometimes he needs to search for types of text rather than a specific character. For example, he might want to find the first, the Nth, or the last occurrence of any bold character (or any italics character) in the cell.
There is no intrinsic function or tool in Excel to accomplish this task. The regular Find and Replace feature returns matches for formatting, but not the position at which those matches occur. To get that specific about what you want to find, you'll need to use a macro. You can get the positioning information back into your worksheet if implement the macro as a user-defined function.
The following macro accepts a range (intended to be a specific cell), an indicator of whether you want bold or italic (or both), and the occurrence of that formatting.
Function FindNth(r As Range, sType As String, N As Integer) As Integer Dim J As Integer Dim iCount As Integer Dim sStyle As String If r.Count = 1 Then FindNth = 0 iCount = 0 For J = 1 To Len(r.Text) sStyle = r.Characters(J, 1).Font.FontStyle If LCase(sStyle) = LCase(sType) Then iCount = iCount + 1 If N = 0 Then FindNth = J Else If N = iCount Then FindNth = J Exit For End If End If End If Next J Else FindNth = -1 End If End Function
In order to use the macro, use one of the following formulas in your worksheet:
=FindNth(A1, "bold", 2) =FindNth(A1, "italic", 3) =FindNth(A1, "bold italic", 1)
In each case the third parameter specifies which occurrence of the given formatting you want to find. The function returns the character position of that occurrence within the cell. If there is no such occurrence, then 0 is returned. If you specify multiple cells in the first parameter of the function, it returns a -1. If you specify an occurrence of 0, then the character position of the last occurrence of the specified format is returned.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13402) applies to Microsoft Excel 2007, 2010, and 2013.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When you need to get rid of characters in the middle of a cell value, the best way to do it is through the use of macros. ...Discover More
Got a macro that doesn't have quite the right name? You can rename the macro by following these simple steps.Discover More
When creating macros, you often need to process numbers in various ways. VBA allows you to convert a numeric value to an ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.