Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easiest way to extract characters in a cell LEFT or RIGHT of a sym
In other programming languages, I was able to enter a function that said:
"Please extract all characters to the LEFT of this symbol (where any character would be enclosed in quotations including a space) or the RIGHT of this symbol." I've been looking at some of the complex functions In this forum that are being used to do this on a cell and they all seem overly complicated. For example, I want to be able to point to a cell that has a First and Last name (John Smith or Catherine Fitzgerald) and regardless of the number of characters in either the first or last name simply produce the First OR the Last name in the formula cell. Sometimes the delimiter is a space, other times a comma, but I wouldn't want to have to jump through hoops to count the number of characters left or right of the cell contents because for each name it will be different. Similiarly, I would want it to be flexible enough to select whatever symbol(s?) separate the first part from the second without being overly complicated. Am I missing something? I can't believe what I'm seeing as solutions are all that can be done. For now, I'm just using the text to columns to separate them out. Any help would be much appreciated. Cheers! Kimberly Mills |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easiest way to extract characters in a cell LEFT or RIGHT of a sym
=LEFT(A1,FIND(" ",A1)-1)
will return what's left of the first space =MID(A1,FIND(" ",A1)+1,255) will return what's to the right of the first space Something generic =LEFT(A1,FIND(C1,A1)-1) where you would put the delimiter in C1 Given how very dissimilar names can be it is basically impossible to cover all options -- Regards, Peo Sjoblom "Training Goddess" wrote in message ... In other programming languages, I was able to enter a function that said: "Please extract all characters to the LEFT of this symbol (where any character would be enclosed in quotations including a space) or the RIGHT of this symbol." I've been looking at some of the complex functions In this forum that are being used to do this on a cell and they all seem overly complicated. For example, I want to be able to point to a cell that has a First and Last name (John Smith or Catherine Fitzgerald) and regardless of the number of characters in either the first or last name simply produce the First OR the Last name in the formula cell. Sometimes the delimiter is a space, other times a comma, but I wouldn't want to have to jump through hoops to count the number of characters left or right of the cell contents because for each name it will be different. Similiarly, I would want it to be flexible enough to select whatever symbol(s?) separate the first part from the second without being overly complicated. Am I missing something? I can't believe what I'm seeing as solutions are all that can be done. For now, I'm just using the text to columns to separate them out. Any help would be much appreciated. Cheers! Kimberly Mills |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easiest way to extract characters in a cell LEFT or RIGHT of a sym
=LEFT(A1,FIND(" ",A1)-1)
=RIGHT(A1,LEN(A1)-FIND(" ",A1)) Replace the space (between the quotes) by a comma, semicolon, whatever you like. -- Kind regards, Niek Otten Microsoft MVP - Excel "Training Goddess" wrote in message ... | In other programming languages, I was able to enter a function that said: | | "Please extract all characters to the LEFT of this symbol (where any | character would be enclosed in quotations including a space) or the RIGHT of | this symbol." | | I've been looking at some of the complex functions In this forum that are | being used to do this on a cell and they all seem overly complicated. | | For example, I want to be able to point to a cell that has a First and Last | name (John Smith or Catherine Fitzgerald) and regardless of the number of | characters in either the first or last name simply produce the First OR the | Last name in the formula cell. | | Sometimes the delimiter is a space, other times a comma, but I wouldn't want | to have to jump through hoops to count the number of characters left or right | of the cell contents because for each name it will be different. Similiarly, | I would want it to be flexible enough to select whatever symbol(s?) separate | the first part from the second without being overly complicated. Am I missing | something? | | I can't believe what I'm seeing as solutions are all that can be done. For | now, I'm just using the text to columns to separate them out. | | Any help would be much appreciated. | | Cheers! | Kimberly Mills | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Certain Characters and Numbers | Excel Worksheet Functions | |||
Extract characters from a cell | Excel Discussion (Misc queries) | |||
auto delete characters in cell left of @ sign | Setting up and Configuration of Excel | |||
Extract the first few characters | Excel Worksheet Functions | |||
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions |