excel Formula Find help

[Kenja824]

I can do a find to give me the location of an underscore. However some of the cells it turns out will have an underscore previous the one I am looking for.

How can you do a find, that looks for an underscore followed by a number, but you never know what number it will be?


Example of the text....

"30B1556 on ESG_TMP-23_43@281"

I dont care about the first underscore in this case. I want the location of the second underscore. But most cells do not have two underscores so I cannot look for the nth underscore. I need the underscore that is followed by a number. This way all cells will be finding the correct underscore.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[davidbeach]

Brute force would be looking for any one of the 10 underscore digit combinations. Certainly there’s something more elegant.

I’ll see your silver lining and raise you two black clouds. - Protection Operations

 

[SkipVought]

Here's a User Defined Function (UDF) in the attached workbook.

Function WhereIsChar(sValue As String, sChar As String) As Integer
'SkipVought 2923 Aug 1
'returns character position of sChar if character to the right is numeric
'otherwise returns ZERO
    Dim a, i As Integer
    a = Split(sValue, "_")
    WhereIsChar = Len(a(0)) + 1
    For i = 1 To UBound(a)
        If IsNumeric(Left(a(i), 1)) Then
          WhereIsChar = WhereIsChar
          Exit Function
       End If
       WhereIsChar = WhereIsChar + Len(a(i)) + 1
    Next
    WhereIsChar = 0
End Function

You must Enable Macros to view or run.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

And here's a spreadsheet formula that works in Excel 2019


[tt]=SUM(IFERROR(FIND({"_0","_1","_2","_3","_4","_5","_6","_7","_8","_9"},A2),0))[/tt]

Where A2 contains the text.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Alternatively, the find list could be in a Structured Table named tFindList on a hidden sheet, for instance, a much superior type of approach that trades on the ease of maintaining & modifying DATA as opposed to CODE...


=SUM(IFERROR(FIND(tFindList,A2),0))

...and enter as an Array Formula


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Awesome
Thanks skip

As often happens to me though, I am pulled onto something else that is hot and I will need a couple more days before I cant test this out.

THank you

As always, you are a great help.



Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.