×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

excel Formula Find help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close