Excel in the IF function how to capture what I need? Cooperate the if use left, right, the mid function, how to intercept the content, I need, as shown in the if function, according to the building of A column, floor, library,,,,, and screenshots later; The judge if there is no mention, the show is 1, because the nature of work, you need to determine the content of the Excel to tens of thousands of article, please the great god teach

CodePudding user response:

With function can't do it, with VBS, can use the following VBS function,
Method one:
For each string from right to left to find the first number, found after consecutive Numbers back

Method 2:
According to your request from right to left to find keywords (building, floor, a library, seat, number, road, etc.), find the consecutive number after the front return,
InStrRev function can return a string inside another string in the first position, the search starts at the end of the string, but returned to the location of the counting from the starting point of the string,

InStrRev function can return the value of the following:
If string1 "" (zero length) - InStr returns 0
If string1 to Null - InStr returns Null
If string2 "" - InStr returns start
If string2 to Null - InStr returns Null
If there is no find string2 - InStr returns 0
If found in string1, string2 InStr returns a string position match is found,
If the start & gt; Len (string1) - InStr returns 0

CodePudding user response:

 Function room (As a Range) 
Dim As Object b 
Application. The Volatile 
With CreateObject (" vbscript. Regexp ") 
The Global=True 
. The Pattern="((\ d +)?=[| | | level library city road no. | | | | room card]) 
"The Set b=. The Execute (Dr. Alue) 
If the test (Dr. Alue) Then 
Room number=b.I tem (biggest mount - 1) 
The Else 
Room number=1 
End the If 
End With 
End the Function