What I would like to see in Excel 2016

Yes Rick.
This is exactly what I mean.
Ah, I see. First thoughts are that yes, that would be useful but I can also see a "can of worms" about how people might want it to work.

I'm sure some people would see the words below as a word, not embedded in another word, while others would not.
I too cannot see Rick's code so I don't know how his code would handle these, but in any case it doesn't really matter as Robert is wishing for a native worksheet function to do the job. :)

My 'can' worksheet has a blue tab
tin.can@tinned.soup.com
It was a can-style structure
I like Ross' car
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi Guys,

This seems to be interesting tread.

The most common problem (working in E-Commerce Industry) is unable to concatenate an array with a assigned delimiter, which can also has the feature of ignoring the blanks. Although I have the Add-In Multicat whic is pretty useful, but would like but see it inbuild.

Thanks/Raj
 
@Peter and Robert

I am having the administrators see if the can unbind my sub-forum from their over-riding rule about making people register in order to see my articles. In the meantime, for your "enjoyment", here is the article in question...

InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word<!-- google_ad_section_end -->

The InStr function is a handy tool. It will return the character position of text that is embedded within a larger text string. And while this is handy, the function is useless for finding words as stand-alone words in the text. Let's say you had this text string...

TextString = "Don't tell me, but your name is Don, right?"

and you wanted to find the character position of the name Don in that text. If you used the InStr function like this...

MsgBox InStr(TextString, "Don")

It would return the number 1 because it would find the letters "Don" inside the word "Don't" and never look further into the text to see if Don existed as a stand-alone word or not. Even using InStr's optional arguments and having it perform a binary search would not help because the letter casing for the first 3 characters of "Don't" is the same a for the name "Don". Here is a function which can be called from your <ACRONYM title=vBulletin>VB</ACRONYM> code or which can be used as a UDF on a worksheet, if desired, that will find text as stand-alone words that are not embedded as part of other words...

Code:
Function InStrExact(Start As Long, SourceText As String, WordToFind As String, _
                    Optional CaseSensitive As Boolean = False, _
                    Optional AllowAccentedCharacters As Boolean = False) As Long
  Dim x As Long, Str1 As String, Str2 As String, Pattern As String
  Const UpperAccentsOnly As String = "ÇÉÑ"
  Const UpperAndLowerAccents As String = "ÇÉÑçéñ"
  If CaseSensitive Then
    Str1 = SourceText
    Str2 = WordToFind
    Pattern = "[!A-Za-z0-9]"
    If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAndLowerAccents)
  Else
    Str1 = UCase(SourceText)
    Str2 = UCase(WordToFind)
    Pattern = "[!A-Z0-9]"
    If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAccentsOnly)
  End If
  For x = Start To Len(Str1) - Len(Str2) + 1
    If Mid(" " & Str1 & " ", x, Len(Str2) + 2) Like Pattern & Str2 & Pattern Then
      InStrExact = x
      Exit Function
    End If
  Next
End Function

This function takes three required arguments and provides for two additional optional arguments. The first argument, named Start, is required and lets you set the starting character number for the search to begin at (use 1 to start the search at the first character). The second argument, named SourceText, is required and is the text to look for the word in. The third argument, named WordToFind, is required and, as the name indicates, is the word whose position you want to locate. The fourth argument, named CaseSensitive, is optional and allows you to make the search case sensitive or not (the default is FALSE representing a case insensitive search). The fifth argument, named AllowAccentedCharacters, is optional and allows certain accented letters to be considered as being part of a word (the default is False meaning only normal ASCII characters can make up the word). For example, with the default value of False, the word "resumé" would never be found because the accented "e" (é) would be considered a non-letter. Setting the fifth argument to True would allow the function to find the word "resumé". I only allowed for three accented letters "ç", "é" and "ñ" (in both lower and upper case), but you can add more if you wish by modifying the UpperAccentsOnly and UpperAndLowerAccents constants (the Const statements at the beginning of the code).<!-- google_ad_section_end -->
 
That's really amazing!!
Thank you very much for your nice comment Raj, I really appreciate it.


I too cannot see Rick's code so I don't know how his code would handle these, but in any case it doesn't really matter as Robert is wishing for a native worksheet function to do the job. :)

My 'can' worksheet has a blue tab
tin.can@tinned.soup.com
It was a can-style structure
I like Ross' car
In case you haven't test the code I posted yet, I'll just let you know that my InStrExact function (correctly I presume) finds the position of the word "can" in each of those example texts (except for the last one which does not contain the word "can"... it returns 0 for that one) because it is not embedded within another word (such as would be the case if the word "scant" were included in the text).
 
Thank you Rick.
You are quite welcome... hopefully you will find the function useful when you are in need of its particular functionality.

And I want to thank you back for alerting me to the registration requirement to see the code in my mini-blog articles... I really was completely unaware of it. I have asked the forum administrators there to look into it. They have that register-to-see-code requirement as a policy for their full forum but they are checking if they can free my sub-forum out from under that requirement. If they can't, I have a possible work-around, but I don't know what the ramification would be of implementing it. I did a test yesterday and noted that if I change the code tags that surround the code to HTML tags (HTML is what's in the brackets), then the code appears to display correctly (all spacing remains in tact) and is visible without registering, but I do not know if using the HTML tags will, or could, misinterpret some VB code syntax for whatever it is that the HTML tags look for to process, so I am hesitant to simply plow through and make the change until I hear back from the forum administrators.
 
You are welcome Rick.
I registered on the Fox a long time ago but has not participated yet.
I saw that ExcelForum has implemented the same policy - no registration - no visibility.
(In some cirsumstances even formulas are not visible).
I hope that MrExcel will remain " see free" forum.
 
You are welcome Rick.
I registered on the Fox a long time ago but has not participated yet.
I saw that ExcelForum has implemented the same policy - no registration - no visibility.
(In some cirsumstances even formulas are not visible).
I hope that MrExcel will remain " see free" forum.
I'll thank you one last time (without your comment, I would never have known there was a problem)... the administrator have implemented a fix and my portion of the ExcelFox forum is now completely visible for all to see (you still have to register to leave a comment, but that is acceptable to me). So you, and everyone else, are now free to see all the "words of wisdom" I have posted over there. If you (or anyone else) is of a mind to see what I have posted, here is a link to my sub-forum (mini-blog) site...

Rick Rothstein's Corner
 
I'd like to see
1) an option for undercase am/pm label. Can do it now with Text function but very inefficient.
2) DSUM, DMAX, DAVERAGE etc. functions that you can define without having to specificy a fix column span value that breaks the formula when you insert columns. Having to keep track of these creates a lot of work. Why can't they be dynamic like other formulas?
 
Last edited:

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top