Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting certain information from cells
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
filling information in cells | Excel Discussion (Misc queries) | |||
Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
Use functions when cells are merged | Excel Worksheet Functions | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) |