Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Did
 
Posts: n/a
Default 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.
  #2   Report Post  
Biff
 
Posts: n/a
Default

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.



  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.



  #4   Report Post  
Did
 
Posts: n/a
Default

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.




  #5   Report Post  
Biff
 
Posts: n/a
Default

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.








  #6   Report Post  
Did
 
Posts: n/a
Default

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.




  #7   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.






  #8   Report Post  
Biff
 
Posts: n/a
Default

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.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
last digit in string gets changed to 0 (16 characters long) Mike Milmoe Excel Discussion (Misc queries) 3 June 29th 05 03:36 PM
Select Text Files from Combobox D.Parker Excel Discussion (Misc queries) 4 June 28th 05 03:46 PM
want to remove all text characters equal to one character in length from text string [email protected] Excel Worksheet Functions 1 April 18th 05 12:25 AM
Insert characters in a text string jamae918 Excel Worksheet Functions 1 March 28th 05 10:04 PM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM


All times are GMT +1. The time now is 02:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"