Extracting certain information from cells
 

I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and only
the "c")

Extracting certain information from cells
 

Try this:

=MID(A1,3,1)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RobMack" wrote in message
...
I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and
only
the "c")

Extracting certain information from cells
 

Same position in each cell?

=MID(A1,3,1)

and copy down as needed

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"RobMack" wrote in message
...
I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and
only
the "c")

Extracting certain information from cells
 

=mid("abcde",3,1)

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"RobMack" wrote:

I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and only
the "c")

Extracting certain information from cells
 

Assuming all of your data follows the same format, then this should work:

=MID(A1,3,1)

A1 is the cell of your original text
3 is the character position you want to start extracting from
1 is the number of characters extracted

HTH,
Elkar

"RobMack" wrote:

I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and only
the "c")

Extracting certain information from cells
 

Hi all,

What if you want to extract data that are in a varied format; ie:

A1: Smith, Dr. John
A2: Franks, Dr. Beans

I only want to pull the surname, but each are of differing length --
suggestions?

Tnx.
Pete


--
SlipperyPete
------------------------------------------------------------------------
SlipperyPete's Profile: http://www.excelforum.com/member.php...o&userid=31220
View this thread: http://www.excelforum.com/showthread...hreadid=506946

Extracting certain information from cells
 

Your example is *not* really a varied "format", just a varied length, since
the comma can be used as the "end-point".

=LEFT(A1,FIND(",",A1)-1)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"SlipperyPete"
wrote in message
news:SlipperyPete.22tcqz_1139238906.7103@excelforu m-nospam.com...

Hi all,

What if you want to extract data that are in a varied format; ie:

A1: Smith, Dr. John
A2: Franks, Dr. Beans

I only want to pull the surname, but each are of differing length --
suggestions?

Tnx.
Pete


--
SlipperyPete
------------------------------------------------------------------------
SlipperyPete's Profile:
http://www.excelforum.com/member.php...o&userid=31220
View this thread: http://www.excelforum.com/showthread...hreadid=506946

Extracting certain information from cells
 

OK -- thanks for the firstname suggestion; no problem.

For extracting the lastname, I've tried and can't seem to get a
function to consistently work. It seems the length of the surname is
creating problems....suggestions?

eg.
Bar, Dr. Drinks
Help, Dr. Doctor
Banana, Dr. Fruity
Hippopotomous, Dr. Hairy

Using a function like this:
=RIGHT(A14,FIND("Dr. ",A14))

Returns results like this:
Drinks
Doctor
r. Fruity
omous, Dr. Hairy

Is there a function that will extract consistently everything from the
right of "Dr. "?

Show me the light -- I'm sick of playing around with this!!
Thanks!!!
Pete


--
SlipperyPete
------------------------------------------------------------------------
SlipperyPete's Profile: http://www.excelforum.com/member.php...o&userid=31220
View this thread: http://www.excelforum.com/showthread...hreadid=506946

Extracting certain information from cells
 

Try this:

=MID(A1,FIND(".",A1)+2,100)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"SlipperyPete"
wrote in message
news:SlipperyPete.238min_1139951407.9633@excelforu m-nospam.com...

OK -- thanks for the firstname suggestion; no problem.

For extracting the lastname, I've tried and can't seem to get a
function to consistently work. It seems the length of the surname is
creating problems....suggestions?

eg.
Bar, Dr. Drinks
Help, Dr. Doctor
Banana, Dr. Fruity
Hippopotomous, Dr. Hairy

Using a function like this:
=RIGHT(A14,FIND("Dr. ",A14))

Returns results like this:
Drinks
Doctor
r. Fruity
omous, Dr. Hairy

Is there a function that will extract consistently everything from the
right of "Dr. "?

Show me the light -- I'm sick of playing around with this!!
Thanks!!!
Pete


--
SlipperyPete
------------------------------------------------------------------------
SlipperyPete's Profile:
http://www.excelforum.com/member.php...o&userid=31220
View this thread: http://www.excelforum.com/showthread...hreadid=506946

Extracting certain information from cells
 

On Tue, 14 Feb 2006 15:08:50 -0600, SlipperyPete
wrote:


OK -- thanks for the firstname suggestion; no problem.

For extracting the lastname, I've tried and can't seem to get a
function to consistently work. It seems the length of the surname is
creating problems....suggestions?

eg.
Bar, Dr. Drinks
Help, Dr. Doctor
Banana, Dr. Fruity
Hippopotomous, Dr. Hairy

Using a function like this:
=RIGHT(A14,FIND("Dr. ",A14))

Returns results like this:
Drinks
Doctor
r. Fruity
omous, Dr. Hairy

Is there a function that will extract consistently everything from the
right of "Dr. "?

Show me the light -- I'm sick of playing around with this!!
Thanks!!!
Pete

There are a lot of ways to skin a cat. Something called regular expressions
are designed for this kind of text manipulation. They can be implemented by
downloading and installing Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then the following formulas can be used:

First Word (i.e. the Last Name)

=REGEX.MID(A1,"\w+")

Last Word (i.e. the first name)

=REGEX.MID(A1,"\w+",-1)

Word after "Dr. " (on your data set will return the same as Last Word):

=REGEX.MID(TRIM(A1),"(?<=Dr. )\w+")

All words after "Dr. ": (i.e. Banana, Dr. Fruity Hairy -- Fruity Hairy)

=REGEX.MID(TRIM(A1),"(?<=Dr. ).*")

and many other possibilities.




--ron