How to find a word after a specific word?-CodePudding

I need to find the one word after the specific word If you guys have any idea in the Formula.

Eg: I have in column D2

P/N:101174 JOYSTICK, 2 AXIS, DEUTSCH

So the word after P/N: is 101174 //this is the answer that i want

I am using the formula :

=TRIM(LEFT(SUBSTITUTE(TRIM(MID($D2,SEARCH("P/N",D2) LEN("P/N") 1,LEN(D2)))," ",REPT(" ",100)),COLUMN(D:D)*100))

the outcome will be :

101174 JOYSTICK, 2 AXIS,

And what the formula means? because i also google it. Especially the 100 at the last sentence is what for ?

CodePudding user response：

Not sure I understand exactly what it is you're aiming to do. Taking a best guess approach, I think this does what you're aiming for:

=MID(D2,SEARCH("P/N:",D2) 4,SEARCH(" ",D2,SEARCH("P/N:",D2) 4)-SEARCH("P/N:",D2)-4)

What is does:
a) MID gets characters from a string, starting at a given point and for a given number of chars
b) SEARCH("P/N:",D2) 4 provides the starting point by finding where P/N: starts then adding its own length 4 to get the next char's starting point
c) SEARCH(" ",D2,SEARCH("P/N:",D2) 4) finds the position of the first space after the location of "P/N:" (using the same logic as b)
d) -SEARCH("P/N:",D2)-4 deducts the position of where we started (to get the word length)

In all cases, 4 is the length of the reference/starting 'word'.

The above could be simplified greatly if P/N: (or whatever the first word is) was always at the left.
But that seems at odds with the OP (which implies there could be preceding characters).

If it is the case that the reference/starting 'word' is always at the left, then you can replace searching for that with the following (Where 5 is the length of the leading 'word' 1 and 4 is the length of the leading 'word'):

=MID(D2,5,SEARCH(" ",D2,4)-5)

Additional info: SEARCH does case in-sensitive search. Use FIND if you want the search to be case-sensitive.

CodePudding user response：

Try this: =TRIM(MID(D10,LEN(LEFT(D10, SEARCH("P/N:",D10,1) 4)),SEARCH(" ",D10,1)-4))

CodePudding user response：

From you current example it seems FILTERXML() would be best fit. Try-

=FILTERXML("<t><s>"&SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[contains(., 'P/N')]"),":","</s><s>")&"</s></t>","//s[last()]")