Extract specific words from cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Santa-D - 15 Jun 2006 03:13 GMT

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?

Reply to this Message

Ron Rosenfeld - 15 Jun 2006 03:54 GMT

>I've been going through various postings and I haven't been able to
>find anything that helps me.
[quoted text clipped - 16 lines]
>
>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

Reply to this Message

Harlan Grove - 15 Jun 2006 04:48 GMT

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.

Reply to this Message

Ron Rosenfeld - 15 Jun 2006 13:52 GMT

>Ron Rosenfeld wrote...
>...
[quoted text clipped - 17 lines]
>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

Reply to this Message

Harlan Grove - 15 Jun 2006 16:51 GMT

Ron Rosenfeld wrote...
...

>or even (assuming only letters and digits within the registration number):
>
[quoted text clipped - 3 lines]
>
>=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)

Reply to this Message

Ron Rosenfeld - 15 Jun 2006 17:30 GMT

>Ron Rosenfeld wrote...
>...
[quoted text clipped - 11 lines]
>
>=REGEX.MID(A10,"\b[^\[\]]*\b",2)

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

--ron

Reply to this Message

Santa-D - 19 Jun 2006 04:03 GMT

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)

Reply to this Message