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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Do you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. You ...Discover More
Normally Excel positions a UserForm in the center of your screen. You may want to position the form elsewhere, more specific ...Discover More
Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.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.