Wildcards but numbers only

Hi guys,

I do not want VBA code, I need a function in my worksheet.

I have this code so far:

Please Login or Register  to view this content.

I need to find the occurances of *** within Column B.
where * = [0-9] (Any number between 0 and 9 but no other characters)

eg, if Cell B4 = "Something235MoreText" I would need a formula to return 9
eg, if Cell B5 = "Something!35MoreText" I would need a formula not to return anything.

Thanks,
Jimmy

I dont understand

Based on sample 1, you to find any numbers within the 1st 9 characters?

Use Flash fill to extract the numbers in a helper column. Then in the formula column

Formula:

Please Login or Register  to view this content.

and fill down.

Originally Posted by FlameRetired

Use Flash fill to extract the numbers in a helper column. Then in the formula column

Formula:

Please Login or Register  to view this content.

and fill down.

Nope, doesn't work.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by FDibbins

I dont understand
Based on sample 1, you to find any numbers within the 1st 9 characters?

Sorry I haven't made myself clear.
This is what my current function does:

Please Login or Register  to view this content.

If the Cell contains: "something120awduh" and YYY = 1. Then it returns the number 10. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "s1me798awduh" and YYY = 1. Then it returns the number 5. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "s12eth026awduh" and YYY = 1. Then it returns the number 7. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "somethi1204awduh" and YYY = 1. Then it returns the number 8. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "something120awduh" and YYY = 1. Then it returns the number 10. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "something120awduh" and YYY = 2. Then it returns the number 11. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "so233hi204awduh" and YYY = 2. Then it returns the number 8. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "678i204awduh" and YYY = 2. Then it returns the number 5. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "678i20434awduh" and YYY = 1. Then it returns the number 1. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "678i20434awduh" and YYY = 2. Then it returns the number 5. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "678i20434awduh" and YYY = 3. Then it returns the number 6. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)
If the Cell contains: "678i20434awduh" and YYY = 4. Then it returns the number 7. As this is where the position of the YYYth occurrence of *** Occurs ( where *** is any number between 1 and 9)

I have attached a spreadsheet below with this information in it.

This code:

Please Login or Register  to view this content.

Would be able to solve it, If excel had a character that represented any number between 1 and 9.

example of what i am looking for.xlsx

Attached is examples of what should happen.

=FIND(CHAR(1),SUBSTITUTE(A1,"XXX",CHAR(1),YYY)) can only find a particular number that I plug into XXX. I need it to work for any possible number XXX

Try below array formula

=IFERROR(SMALL(IF(ISNUMBER(MID(SUBSTITUTE(C2," ","^"),ROW(INDIRECT("1:"&LEN(C2)-2)),3)+0),ROW(INDIRECT("1:"&LEN(C2)-2))),1),"")
the colored one is YYY = 4 change is as per your need


..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Ok we're getting closer. but let me try again once more.

Let X represent any number [1-9]
Let C represent any letter [A-Z]
Let * represent any letter or Number [A-Z, 1-9]
Let all other letters of the alphabet stay the same.
Let all other symbols stay the say


I want to be able to find the first, Second, Third, fourth...(etc) occurrence of:

of any length of substring make up of X, C or * in any order.

Eg
1st, C*X
100th, **
23rd, X
87th, CXCXCXCSomethingXCXCX**
2nd, *XCething
4th, $
3rd, *omethCng (%^

I'll keep playing around with it, but hopefully you get what I'm looking for.
Thanks so far guys.

Attachment 560152

Hi Jose

I like your formula. I can see how it could work for any digits of any length. but I am wondering about finding substrings with wildcard-digits within the substring.
It's getting closer!


thanks,
Jimmy.

*Bumping*
Still an active problem.

Jimmy, it doesnt look like that attachment in post 13 came through?

Hi guys,

I realize my original question was answered, (Thanks!)
but this very much related, and related to wildcards and searching in strings and is actually what I need.


I'm looking for a function where I can search for sub-strings within other strings; search inputs includes wildcards, any length of sub-string, the occurrence of sub string.
I think the attached sheet shows a good example.
(I don't mind what symbols are used as long as they're not common)
(The formula I supplied =FIND(CHAR(1),SUBSTITUTE(A1,"XXX",CHAR(1),YYY), would actually work, if it knew what each symbol represented. Does Excel have any wildcards built in?)

Again, ideally I'm looking for something I can use on Sheets, preferably not a sub in VBA.
However *I think* creating a worksheet function in VBA might be the right approach? I've only ever created one or two of them before and am a bit skeptical, and also a bit thrown by how to process the wildcards.


Thanks guys,
Jimmy

*Bumping this thread.*

*Bumping this thread.*

*Bumping this tread*

You've had to bump it three times now. May I suggest you try reframing your question from post #16? Do that here and then maybe someone will understand what you are after and be able to help.

@AliGW Personally I have been ignoring this thread because I am waiting for a reply on another of Jimmy's threads which makes all the above bumps seem quite ironic.

@Jimmy It is very frustrating trying to help people who "drop" their threads with no explanation
Solution providers get satisfaction from knowing whether or not a suggestion got you to where you wanted to go - and probably learn more from getting it wrong than getting it right
Leaving things "in mid air" is unhelpful to future readers of a thread
A thread is not only for the originator's benefit - it can help lots of other people if everyone plays the game properly

Getting back to this thread
Caveat: This is more helpful pointer than complete solution.
Familiarise yourself with RegEx (Regular expressions) - there are no shortcuts - it takes a bit of effort which will be well rewarded later.
Look it up on google, here is one place to start
Regex can do everything you require and vastly more than that - a few examples using the 3 UDF's below

regex patterns.jpg

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.