ExcelBanter - Search for a character subsequent to its first appearance

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Search for a character subsequent to its first appearance (https://www.excelbanter.com/excel-worksheet-functions/51535-search-character-subsequent-its-first-appearance.html)

Henrik

Search for a character subsequent to its first appearance

Hi,

The worksheet function SEARCH finds the first appearance of a character or
text string within another text string (or array of text strings). Does
anyone know a way to 'tweak' SEARCH to look for the Xth appearance of a
character. For instance, in

"Brian M. Johnson", I would like to search for the second "space" and have
my fuction return 9.

Your help is much appreciated.

Aladin Akyurek

Search for a character subsequent to its first appearance

=SEARCH("#",SUBSTITUTE(A2," ","#",2))

Henrik wrote:
Hi,

The worksheet function SEARCH finds the first appearance of a character or
text string within another text string (or array of text strings). Does
anyone know a way to 'tweak' SEARCH to look for the Xth appearance of a
character. For instance, in

"Brian M. Johnson", I would like to search for the second "space" and have
my fuction return 9.

Your help is much appreciated.

All times are GMT +1. The time now is 07:28 AM.