Avatar of Euro5
Euro5Flag for United States of America asked on

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


Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Martin Liss

is "ARRE, JUAN      , JUAN" the contents of cell C4?
byundt

Try +1 for the right side. Also, consider using MID.
=MID(C4,FIND(",",C4 & ",")+1,99)

Open in new window

I concatenated a comma to the end of C4 in the FIND to avoid an error if the C4 text doesn't contain a comma.

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
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
byundt

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
byundt

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 & ",")))

Open in new window

ASKER
Euro5

Yes, Martiin.
 is "ARRE, JUAN      , JUAN" the contents of cell C4?
Rob Henson

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"

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.