by Allen Wyatt
(last updated February 17, 2020)
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 you 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, 2013, 2016, 2019, and Excel in Office 365.
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!
Want to use a worksheet function (such as SUM) from within a macro? Here's how easy it is to accomplish the task.Discover More
Using a specialized calendar control is a great way to let users add dates to a worksheet. Unfortuantely, Microsoft ...Discover More
Does your macro need to change the width of some columns in a worksheet? Here's how to do it.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.