Formula

[jalenben1]

If I have a cell in Column A and I want to pull the last name in Column B what formula would I write

Column A ColumnB
Dwayne Harris Harris

 

[dhookom]

Something like:

=MID(B1,FIND(" ",B1)+1,30)

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

I am about to head out of the office, so I don't have time to look up what I can't remember. However, I think you could use the SPLIT function as one option. And of course, you can use the RIGHT function in conjunctiong with getting the character count for where the space is. And for finding the character count where the space is located, you'll need the FIND function, I believe... well, hopefully that'll get you started...

The Formula could be something like (In Cell B2):

=RIGHT(A2,FIND(" "))

But I'm pretty sure I'm forgetting something. Post back if anything doesn't make sense, or if you find something different. I'm sure someone else will come along and give the perfect solution.

If I get a chance, I'll check back later this evening.

 

[kjv1611]

See, shoulda looked before I clicked "submit".

 

[jalenben1]

Ii figured it out....I came up with

=RIGHT(A1,6)and it worked.

 

[Gruuuu]

jalen... that will ONLY work if the last name is 6 characters long.
If you changed the name to John Doe:

A             B
John Doe   hn Doe

You will want to use a function that finds the location of the space in the name, such as FIND.
May I suggest:

=MID(A1, FIND(" ",A1)+1,LEN(A1) - FIND(" ",A1))
the MID function returns a number of characters from a starting point of a string.

MID(string, starting point, number of characters)

The FIND function tells us that the space is the 5th character, therefor:

MID("John Doe",5+1 (6), 8-5 (3))

Which returns three characters, starting from the 6th, of the string of characters "John Doe". Make sense?

 

[SkipVought]

Hi,

Generally speaking, there is no answer that will be correct for ANY instance of a given first and last name. BOTH first and last names can have multiple words, like Jo Ann De Bore, or Kurt Van der Voort.

So the FIRST thing that you have to do is some analysis to determine how many cases, if any, where the first and last names are single words (1:1). If there ARE other instances, they must be categorized together in order to apply their own unique logic.

For the 1:1 instances, probably the majority, I would use the Data > Text to columns -- DELIMITED by SPACE. This will put the FIRST word in the original column and the SECOND word in the adjacent column. No formula required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}