×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# excel Formula Find help

## excel Formula Find help

(OP)
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
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.

### RE: excel Formula Find help

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

### RE: excel Formula Find help

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

#### CODE

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,

Just traded in my OLD subtlety...
for a NUance!

### RE: excel Formula Find help

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

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

Where A2 contains the text.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: excel Formula Find help

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,

Just traded in my OLD subtlety...
for a NUance!

### RE: excel Formula Find help

(OP)
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
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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!