select a string of characters

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>