Extracting certain information from cells

R

RobMack

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")
 
R

RagDyer

Try this:

=MID(A1,3,1)

--
HTH,

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

Ken Wright

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 :)
------------------------------­------------------------------­----------------
 
E

Elkar

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
 
S

SlipperyPete

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
 
R

RagDyeR

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

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
 
S

SlipperyPete

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
 
R

RagDyer

Try this:

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

--
HTH,

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


"SlipperyPete" <[email protected]>
wrote in message
news:[email protected]...
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top