Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-06-2020, 03:33 PM
14spar15 14spar15 is offline Using wildcards with SEARCH Windows 7 64bit Using wildcards with SEARCH Office 2010 64bit
Advanced Beginner
Using wildcards with SEARCH
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Using wildcards with SEARCH

Hello, I’m trying to find a formula that will extract any “-“ that is sandwiched between any two characters that are not spaces. i.e. Q-5, h-k, 9-0. I was thinking this would work to find them, =SEARCH("?-?",B2), but it does not because apparently a blank space qualifies as a character in this case. It would be helpful if the formula would work for more than one instance of the situation in any given string if the situation occurred more than once. Thank you
Reply With Quote
  #2  
Old 10-07-2020, 12:06 AM
Purfleet Purfleet is offline Using wildcards with SEARCH Windows 10 Using wildcards with SEARCH Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Can you add an examples worksheet so we doint have to waste time recreating the data and possibly getting it wrong?


Try =IF(AND(MID(A1,SEARCH("-",A1)-1,1)=" ",MID(A1,SEARCH("-",A1)+1,1)=" "),A1,SUBSTITUTE(A1,"-",""))
Attached Files
File Type: xlsx Using wildcards with SEARCH_Purfleet.xlsx (9.0 KB, 6 views)
Reply With Quote
  #3  
Old 10-07-2020, 12:17 AM
14spar15 14spar15 is offline Using wildcards with SEARCH Windows 7 64bit Using wildcards with SEARCH Office 2010 64bit
Advanced Beginner
Using wildcards with SEARCH
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

I need to spend some time with this and figure out how this works because it does exactly what I need. Thanks so much
Reply With Quote
  #4  
Old 10-07-2020, 09:37 AM
14spar15 14spar15 is offline Using wildcards with SEARCH Windows 7 64bit Using wildcards with SEARCH Office 2010 64bit
Advanced Beginner
Using wildcards with SEARCH
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Oops!, Still a problem

Unfortantly I spoke too soon. The strings I'm using have some "-" that don't have spaces on either side and some that do. I only want to delete the ones that do not have spaces on either side. With this formula, if the first "-" intance does have spaces all of the "-" in the string are deleted. If the first instance does not have spaces none of them are deleted. Do you think there may be a fix for this? I was thinking I could split the string up at each space and then test each cell with your formula or just delete all of the "-" in the cells with more then 1 characters. After this I could reassemble the string. It seems "Text to Columns" could do this a little cleaner but both seem sloppy with strings that can have up to 15 spaces.
Reply With Quote
  #5  
Old 10-07-2020, 09:44 AM
Purfleet Purfleet is offline Using wildcards with SEARCH Windows 10 Using wildcards with SEARCH Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Can you post examples, the more the better,

Its the only way we can test properly - we might just need to change the AND to an OR
Reply With Quote
  #6  
Old 10-07-2020, 10:36 AM
14spar15 14spar15 is offline Using wildcards with SEARCH Windows 7 64bit Using wildcards with SEARCH Office 2010 64bit
Advanced Beginner
Using wildcards with SEARCH
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

I need like this....Thanks
Attached Files
File Type: xlsx Updated_Using wildcards with SEARCH_Purfleet (3).xlsx (10.2 KB, 7 views)
Reply With Quote
  #7  
Old 10-07-2020, 12:48 PM
Purfleet Purfleet is offline Using wildcards with SEARCH Windows 10 Using wildcards with SEARCH Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Right, having checked the formula it was never going to work as it replaces all dashes.

My fault, but does show the value of adding in example data.

So the next attempt, a UDF


Code:
Dim DashNumber As Integer

'Countnumber of dashes for the loop
CountDash = Len(SelectedText) - Len(Replace(SelectedText, Delimit, ""))

'Set output text to start
OutputText = SelectedText

DashNumber = 0

'Loop through all dashes
For I = 1 To CountDash

'Find Dash number in string
    DashNumber = InStr(DashNumber + 1, OutputText, Delimit)
    
    'Check char before dash
    TextBefore = Mid(OutputText, DashNumber - 1, 1)
    'Check char after dash
    TextAfter = Mid(OutputText, DashNumber + 1, 1)
        
        'check if before and after chars are equal to space
        If TextBefore <> " " And TextAfter <> "" Then
            'set outputext as the first chars and then the new text with 1 less dash
            OutputText = Mid(OutputText, 1, DashNumber - 1) & Replace(OutputText, Delimit, "", Start:=DashNumber, Count:=1)

        End If
    Next I
    
RemoveDash = OutputText

End Function

Last edited by Purfleet; 10-07-2020 at 12:48 PM. Reason: typo
Reply With Quote
  #8  
Old 10-14-2020, 08:43 AM
14spar15 14spar15 is offline Using wildcards with SEARCH Windows 7 64bit Using wildcards with SEARCH Office 2010 64bit
Advanced Beginner
Using wildcards with SEARCH
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Sorry for the slow response here. I had to step away from this project for a bit. Your last solution is working perfectly. Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using wildcards with SEARCH Search and replace, with paragraphs and wildcards alex100 Word VBA 3 05-16-2020 08:43 AM
Using wildcards with SEARCH Search and replace wildcards eduzs Word VBA 2 06-29-2019 03:01 AM
Using wildcards with SEARCH Need Search and Replace Help w Wildcards Oberstfunster Word 2 12-06-2018 09:28 AM
Wildcards used for Search and Replace ChrisRick Word 2 03-09-2017 05:01 AM
Using wildcards with SEARCH Search with wildcards imag Word 1 11-05-2014 11:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft