Extract characters from string
I am trying to isolate characters before and after a comma in a cell.
It is not working though, as it is sometimes including the comma as well as other characters.
For instance the following should be JUAN but I am getting , JUAN.
There are other variations.
I need the name before the comma and the name after the comma.
I am using =RIGHT(C4,(FIND(",",C4,1)-1)) and =LEFT(C2,(FIND(",",C2,1)-1))
The LEFT works well but the RIGHT does not.
Can anyone help please!???
| ARRE, JUAN | , JUAN |
Last Comment
Martin Liss
is "ARRE, JUAN , JUAN" the contents of cell C4?
Try +1 for the right side. Also, consider using MID.
The 99 is an arbitrary number. It should be larger than the maximum length of the returned value. Some people use LEN(C4) instead, but I try to simplify.
=MID(C4,FIND(",",C4 & ",")+1,99)
The 99 is an arbitrary number. It should be larger than the maximum length of the returned value. Some people use LEN(C4) instead, but I try to simplify.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If you don't want to use a formula at all, you might use the Data...Text to Columns ribbon item with a comma as the delimiter. The right portion of the text will have a leading space if the original text included one after the comma.
If you want to understand how to use the RIGHT function, consider either of:
=RIGHT(C4,LEN(C4)-FIND(",",C4 & ",")-1)
=TRIM(RIGHT(C4,LEN(C4)-FIND(",",C4 & ",")))
ASKER
Yes, Martiin.
is "ARRE, JUAN , JUAN" the contents of cell C4?
is "ARRE, JUAN , JUAN" the contents of cell C4?
When using FIND it starts at the start of the text so combined with the RIGHT function doesn't always work.
With your example
ARRE, JUAN , JUAN
=RIGHT(C4,(FIND(",",C4,1)-1))
The FIND will be finding the first comma after ARRE, which is the fifth character, then deduct 1. The RIGHT function then takes that figure (4) and takes the four characters from the right hand end of the text. In this instance that works because JUAN is only 4 characters.
With a different example:
BOND, JAMES , JAMES
Comma is character 5, less 1 gives 4, rightmost 4 characters gives "AMES"
BLOGGS, FRED ,FRED
Comma is character 7, less 1 gives 6, rightmost 6 characters gives " ,FRED"
With your example
ARRE, JUAN , JUAN
=RIGHT(C4,(FIND(",",C4,1)-1))
The FIND will be finding the first comma after ARRE, which is the fifth character, then deduct 1. The RIGHT function then takes that figure (4) and takes the four characters from the right hand end of the text. In this instance that works because JUAN is only 4 characters.
With a different example:
BOND, JAMES , JAMES
Comma is character 5, less 1 gives 4, rightmost 4 characters gives "AMES"
BLOGGS, FRED ,FRED
Comma is character 7, less 1 gives 6, rightmost 6 characters gives " ,FRED"
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
