#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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,"-","")) |
#3
|
|||
|
|||
I need to spend some time with this and figure out how this works because it does exactly what I need. Thanks so much
|
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
I need like this....Thanks
|
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Search and replace, with paragraphs and wildcards | alex100 | Word VBA | 3 | 05-16-2020 08:43 AM |
Search and replace wildcards | eduzs | Word VBA | 2 | 06-29-2019 03:01 AM |
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 |
Search with wildcards | imag | Word | 1 | 11-05-2014 11:42 PM |