Avatar of elliottbenzle
elliottbenzle asked on

Excel - subtract string to create a new column

In Excel I have two columns with strings, one has a full name and one has a first name. Like this:

column 1                              column 2
Christina Swartz                 Christina
Ms. Jill A. Swedlow             Ms. Jill A.
Mr. Ralph (Ed) Sweet           Mr. Ralph (Ed)


I want to use some kind of an equation in a third column that will fill that column with only the last name. I don't know excel very well, but I think it would look something like this:

Column C = Column 1 - Column 2

Does anyone know how to do this? or if it's even possible?

Thanks
Microsoft ExcelSpreadsheetsMicrosoft Office

Avatar of undefined
Last Comment
zorvek (Kevin Jones)

8/22/2022 - Mon
zorvek (Kevin Jones)

Use this formula:

=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)

Kevin
ASKER
elliottbenzle

That always enter the word 'first' into the field.
harfang

@Kevin: I think he already has column two...

elliottbenzle,

Please try one of these:

= TRIM(SUBSTITUTE(A1,B1,""))
= TRIM(MID(A1,LEN(B1)+1,1000))
= MID(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))+1,99)
1: replaces the first name with "" (and trim result)
2: skip from A the length of B (and trim result)
3: extract last word from A (based on Kevin's expression)

(°v°)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
harfang

> That always enter the word 'first' into the field.

When posting an expression, we always assume that the data is in cell A1, unless stated otherwise. Put “Christina Swartz” in cell A1 and “Christina” in cell B1, then try the functions in cells C1, D1, etc.

(°v°)
ASKER CERTIFIED SOLUTION
G Trurab Khan

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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.