software

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 quot;cquot; and only
the quot;cquot;)

Try this:

=MID(A1,3,1)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================quot;RobMackquot; gt; wrote in message
...
gt; I am trying to extract only partial information from a cell that contains
gt; mutliple entries without seperator. Ie I want to get 3rd reference from
gt; cell containing abcde(I want to extract onto another sheet the quot;cquot; and
only
gt; the quot;cquot;)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 :-)
------------------------------*------------------------------*----------------quot;RobMackquot; gt; wrote in message
...
gt;I am trying to extract only partial information from a cell that contains
gt; mutliple entries without seperator. Ie I want to get 3rd reference from
gt; cell containing abcde(I want to extract onto another sheet the quot;cquot; and
gt; only
gt; the quot;cquot;)
=mid(quot;abcdequot;,3,1)

HTH,
--
Gary Brown

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

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

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

quot;RobMackquot; wrote:

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


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: www.excelforum.com/member.php...oamp;userid=31220
View this thread: www.excelforum.com/showthread...hreadid=506946Your example is *not* really a varied quot;formatquot;, just a varied length, since
the comma can be used as the quot;end-pointquot;.

=LEFT(A1,FIND(quot;,quot;,A1)-1)

--

HTH,

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

quot;SlipperyPetequot; gt;
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:
www.excelforum.com/member.php...oamp;userid=31220
View this thread: www.excelforum.com/showthread...hreadid=506946

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(quot;Dr. quot;,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 quot;Dr. quot;?

Show me the light -- I'm sick of playing around with this!!
Thanks!!!
Pete--
SlipperyPete
------------------------------------------------------------------------
SlipperyPete's Profile: www.excelforum.com/member.php...oamp;userid=31220
View this thread: www.excelforum.com/showthread...hreadid=506946Try this:

=MID(A1,FIND(quot;.quot;,A1) 2,100)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================quot;SlipperyPetequot; gt;
wrote in message
news:SlipperyPete.238min_1139951407.9633@excelforu m-nospam.com...
gt;
gt; OK -- thanks for the firstname suggestion; no problem.
gt;
gt; For extracting the lastname, I've tried and can't seem to get a
gt; function to consistently work. It seems the length of the surname is
gt; creating problems....suggestions?
gt;
gt; eg.
gt; Bar, Dr. Drinks
gt; Help, Dr. Doctor
gt; Banana, Dr. Fruity
gt; Hippopotomous, Dr. Hairy
gt;
gt; Using a function like this:
gt; =RIGHT(A14,FIND(quot;Dr. quot;,A14))
gt;
gt; Returns results like this:
gt; Drinks
gt; Doctor
gt; r. Fruity
gt; omous, Dr. Hairy
gt;
gt; Is there a function that will extract consistently everything from the
gt; right of quot;Dr. quot;?
gt;
gt; Show me the light -- I'm sick of playing around with this!!
gt; Thanks!!!
gt; Pete
gt;
gt;
gt; --
gt; SlipperyPete
gt; ------------------------------------------------------------------------
gt; SlipperyPete's Profile:
www.excelforum.com/member.php...oamp;userid=31220
gt; View this thread: www.excelforum.com/showthread...hreadid=506946
gt;On Tue, 14 Feb 2006 15:08:50 -0600, SlipperyPete
gt; wrote:

gt;
gt;OK -- thanks for the firstname suggestion; no problem.
gt;
gt;For extracting the lastname, I've tried and can't seem to get a
gt;function to consistently work. It seems the length of the surname is
gt;creating problems....suggestions?
gt;
gt;eg.
gt;Bar, Dr. Drinks
gt;Help, Dr. Doctor
gt;Banana, Dr. Fruity
gt;Hippopotomous, Dr. Hairy
gt;
gt;Using a function like this:
gt;=RIGHT(A14,FIND(quot;Dr. quot;,A14))
gt;
gt;Returns results like this:
gt;Drinks
gt;Doctor
gt;r. Fruity
gt;omous, Dr. Hairy
gt;
gt;Is there a function that will extract consistently everything from the
gt;right of quot;Dr. quot;?
gt;
gt;Show me the light -- I'm sick of playing around with this!!
gt;Thanks!!!
gt;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
xcell05.free.fr/

Then the following formulas can be used:

First Word (i.e. the Last Name)

=REGEX.MID(A1,quot;\w quot;)

Last Word (i.e. the first name)

=REGEX.MID(A1,quot;\w quot;,-1)

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

=REGEX.MID(TRIM(A1),quot;(?lt;=Dr. )\w quot;)

All words after quot;Dr. quot;: (i.e. Banana, Dr. Fruity Hairy --gt; Fruity Hairy)

=REGEX.MID(TRIM(A1),quot;(?lt;=Dr. ).*quot;)

and many other possibilities.

--ron