select a string of characters
 

I have some large cells with various data, some (not all) contain a unique
string of charaters which I would like to extract to another cell. The string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers. Im not
very good at VB, so would prefer a formula in a cell but beggers can't be
chosers. Thanks for any assistance.

Hi!

For the best possible solution you'll need to provide several examples.

Biff

"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers. Im
not
very good at VB, so would prefer a formula in a cell but beggers can't be
chosers. Thanks for any assistance.

Do you want to break it down into component part? If so then use

B2: =IF(A2<"",LEFT(A2,2),"")
C2: =IF(A2<"",MID(A2,3,6),"")
D2: =IF(A2<"",RIGHT(A2,1),"")

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers. Im
not
very good at VB, so would prefer a formula in a cell but beggers can't be
chosers. Thanks for any assistance.

I can e-mail a few of the fields if you want. Each cell is usally around
600-800 characters long of messages relating to a membership, so a lot of, he
said, she said and some where you get things like "casemem no is NR623454C"
or "t/p advised HG674398P won't attend" and "BH346799R to remain linked and
open" its the NR623454C HG674398P BH346799R that I would like to copy to a
new column, leaving it blank if the membership no doesn't appear.

"Biff" wrote:

Hi!

For the best possible solution you'll need to provide several examples.

Biff

"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers. Im
not
very good at VB, so would prefer a formula in a cell but beggers can't be
chosers. Thanks for any assistance.

I think that is going to be very difficult to do!

Probably going to need VBA. I can't help with that,sorry!

Biff

"Did" wrote in message
...
I can e-mail a few of the fields if you want. Each cell is usally around
600-800 characters long of messages relating to a membership, so a lot of,
he
said, she said and some where you get things like "casemem no is
NR623454C"
or "t/p advised HG674398P won't attend" and "BH346799R to remain linked
and
open" its the NR623454C HG674398P BH346799R that I would like to copy
to a
new column, leaving it blank if the membership no doesn't appear.

"Biff" wrote:

Hi!

For the best possible solution you'll need to provide several examples.

Biff

"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a
unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers.
Im
not
very good at VB, so would prefer a formula in a cell but beggers can't
be
chosers. Thanks for any assistance.

Think there was a cross over in the posting, this only works if the sting of
characters I want is the first thing in the cell (which sometimes it is), I
would love to be able for it to find and extract no matter where it is in the
field, as long as it is 2 letters, 6 numbers and a single letter.

"Bob Phillips" wrote:

Do you want to break it down into component part? If so then use

B2: =IF(A2<"",LEFT(A2,2),"")
C2: =IF(A2<"",MID(A2,3,6),"")
D2: =IF(A2<"",RIGHT(A2,1),"")

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers. Im
not
very good at VB, so would prefer a formula in a cell but beggers can't be
chosers. Thanks for any assistance.

That is going to be difficult, even with VBA.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Did" wrote in message
...
Think there was a cross over in the posting, this only works if the sting
of
characters I want is the first thing in the cell (which sometimes it is),
I
would love to be able for it to find and extract no matter where it is in
the
field, as long as it is 2 letters, 6 numbers and a single letter.

"Bob Phillips" wrote:

Do you want to break it down into component part? If so then use

B2: =IF(A2<"",LEFT(A2,2),"")
C2: =IF(A2<"",MID(A2,3,6),"")
D2: =IF(A2<"",RIGHT(A2,1),"")

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a
unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers.
Im
not
very good at VB, so would prefer a formula in a cell but beggers can't
be
chosers. Thanks for any assistance.

Here's one possibility that's relatively easy but whether it will work for
your situation.....

If you have a list of the known member IDs....

Put that list in a range of cells and give that range a name like IDs.

Then use this array formula:

=INDEX(IDs,MATCH(TRUE,ISNUMBER(SEARCH(IDs,A1)),0))

Tested and works on cells with 300 characters.

To return blanks when no ID number is found:

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(IDs,A1)),0))," ",INDEX(IDs,MATCH(TRUE,ISNUMBER(SEARCH(IDs,A1)),0) ))

Biff

"Biff" wrote in message
...
I think that is going to be very difficult to do!

Probably going to need VBA. I can't help with that,sorry!

Biff

"Did" wrote in message
...
I can e-mail a few of the fields if you want. Each cell is usally around
600-800 characters long of messages relating to a membership, so a lot
of, he
said, she said and some where you get things like "casemem no is
NR623454C"
or "t/p advised HG674398P won't attend" and "BH346799R to remain linked
and
open" its the NR623454C HG674398P BH346799R that I would like to copy
to a
new column, leaving it blank if the membership no doesn't appear.

"Biff" wrote:

Hi!

For the best possible solution you'll need to provide several examples.

Biff

"Did" wrote in message
...
I have some large cells with various data, some (not all) contain a
unique
string of charaters which I would like to extract to another cell. The
string
of characters is two alphanumerical then six numerical then one
alphanumerical. For those in the UK, like National Insurance numbers.
Im
not
very good at VB, so would prefer a formula in a cell but beggers can't
be
chosers. Thanks for any assistance.