Extract specific words from cells

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?

On 14 Jun 2006 19:13:46 -0700, "Santa-D" <[email protected]> wrote:

>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

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.

On 14 Jun 2006 20:48:55 -0700, "Harlan Grove" <[email protected]> wrote:

>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

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)

On 15 Jun 2006 08:51:00 -0700, "Harlan Grove" <[email protected]> wrote:

>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

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)