Extract specific words from cells

[Santa-D]

I've been going through various postings and I haven't been able to
find anything that helps me.

I have a list of key numbers with names and registration numbers and I
want to extract the registration number which is kept in a set of
braces.

Key No. 71 Jane Doe - [ PRIVATE ]

However, I'm getting spaces, the last brace in the cell, how do I
remove it all?

so I don't get the following ( _ = space) : _PRIVATE_]

Here is my formula: =MID(L12,FIND("[ ",L12)+1,FIND(" ]",L12)-1)
I even tried to remove more spaces by doing this: =MID(L12,FIND("[
",L12)+1,SUM(FIND(" ]",L12)-11)) but I got the same results so that
didn't work out.

What am I doing wrong?

 

[Ron Rosenfeld]

I've been going through various postings and I haven't been able to
find anything that helps me.

I have a list of key numbers with names and registration numbers and I
want to extract the registration number which is kept in a set of
braces.

Key No. 71 Jane Doe - [ PRIVATE ]

However, I'm getting spaces, the last brace in the cell, how do I
remove it all?

so I don't get the following ( _ = space) : _PRIVATE_]

Here is my formula: =MID(L12,FIND("[ ",L12)+1,FIND(" ]",L12)-1)
I even tried to remove more spaces by doing this: =MID(L12,FIND("[
",L12)+1,SUM(FIND(" ]",L12)-11)) but I got the same results so that
didn't work out.

What am I doing wrong?

1. The start position will be where the [ is located. Specifying "[" or "[ "
will still start at the same location.

2. Your number of characters is being computed incorrectly. If you want to
compute it, you need to take the difference in position between the [ and the
], subtracting the spaces.

So if there is always a single space between the bracket and the registration
number, then:

=MID(A1,FIND("[ ",A1)+2,FIND(" ]",A1)-FIND("[",A1)-2)

If there are variable numbers of spaces, then:

=TRIM(SUBSTITUTE(MID(A1,FIND("[",A1)+2,255),"]",""))

should also work.
--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

So if there is always a single space between the bracket and the registration
number, then:

=MID(A1,FIND("[ ",A1)+2,FIND(" ]",A1)-FIND("[",A1)-2)

Alternative,

=MID(LEFT(A1,FIND("]",A1)-1),FIND("[",A1)+1,255)

replacing a FIND call with a LEFT call.

If there are variable numbers of spaces, then:

=TRIM(SUBSTITUTE(MID(A1,FIND("[",A1)+2,255),"]",""))

should also work.

It won't work if there are any nonblanks after the right square
bracket. Just wrap the first formula in TRIM.

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

So if there is always a single space between the bracket and the registration
number, then:

=MID(A1,FIND("[ ",A1)+2,FIND(" ]",A1)-FIND("[",A1)-2)

Alternative,

=MID(LEFT(A1,FIND("]",A1)-1),FIND("[",A1)+1,255)

replacing a FIND call with a LEFT call.

If there are variable numbers of spaces, then:

=TRIM(SUBSTITUTE(MID(A1,FIND("[",A1)+2,255),"]",""))

should also work.

It won't work if there are any nonblanks after the right square
bracket. Just wrap the first formula in TRIM.

or even (assuming only letters and digits within the registration number):

=REGEX.MID(A10,"\w+(?=\s*])")

or possibly

=REGEX.MID(A10,"[^[\s]\w+(?=\s*])")


--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

or even (assuming only letters and digits within the registration number):

=REGEX.MID(A10,"\w+(?=\s*])")

or possibly

=REGEX.MID(A10,"[^[\s]\w+(?=\s*])")

If you're going to go the regexp route and you want to preserve
repeated spaces within the substring between the possibly space padded
square brackets,

=REGEX.MID(A10,"\b[^\[\]]*\b",2)

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

or even (assuming only letters and digits within the registration number):

=REGEX.MID(A10,"\w+(?=\s*])")

or possibly

=REGEX.MID(A10,"[^[\s]\w+(?=\s*])")

If you're going to go the regexp route and you want to preserve
repeated spaces within the substring between the possibly space padded
square brackets,

=REGEX.MID(A10,"\b[^\[\]]*\b",2)

Verry nice! I was trying something similar, and didn't even think about using
the index parameter.


--ron

 

[Santa-D]

At the end of the day this worked as well.

=MID(D11,FIND("[",$D$11,1)+1,FIND("]",$D$11,1)-FIND("[",$D$11,1)-1)