Remove last letter from entries in a column

[TxWebdesigner]

I have a long list of first names but some have a middle initial after their
first name that I need to drop from the entire list. Sample would be:
"Jennifer A"

Can anyone tell me what function/code I could use to drop that last single
letter from every row in that column, or leave it alone, if there is not a
single letter at the end?

Thank you!

 

[Don Guillett]

Sub striplastltr()
For Each c In Range("g2:g" & Cells(Rows.Count, "g").End(xlUp).row)
If InStr(c, " ") > 0 Then c.value = Left(c, InStr(c, " "))
Next
End Sub

 

[TxWebdesigner]

Hello,

Thank you for your response but I'm not sure I follow....

Am I supposed to create a function based on the below? I am not experienced
in functions or code with Excel so if you could help me understand how I go
about implemeneting the code below, I would appreciate it.

Thank you!

 

[Ron Rosenfeld]

I have a long list of first names but some have a middle initial after their
first name that I need to drop from the entire list. Sample would be:
"Jennifer A"

Can anyone tell me what function/code I could use to drop that last single
letter from every row in that column, or leave it alone, if there is not a
single letter at the end?

Thank you!

Here is a formula that should strip off the last <space><letter> combination
provided there are zero or one spaces in the name.

=IF(ISNUMBER(SEARCH(" ",A1)),IF(SEARCH(" ",A1)+1=LEN(A1),
LEFT(A1,SEARCH(" ",A1)-1),A1),A1)

If there might be more than one space in the name, post back with more
examples.
--ron

 

[T. Valko]

Here's another one.

=IF(ISNUMBER(SEARCH(" ?xx",A1&"xx")),LEFT(A1,LEN(A1)-2),A1)

Biff

 

[Dave Peterson]

One based on Biff's idea:

=IF(COUNTIF(A1,"* ?")=0,A1,LEFT(A1,LEN(A1)-2))

 

[TxWebdesigner]

Hello,

Thank you all for help - that one worked beautifully! I have one more I
could use help with...

I have some that have 2 words as their first name, as opposed to just 1.
Some of them even have 3 words as their first name. How do I get it to keep
just the first word of the entire name from every row in this column?

Thank you in advance!!!

 

[Ron Rosenfeld]

Hello,

Thank you all for help - that one worked beautifully! I have one more I
could use help with...

I have some that have 2 words as their first name, as opposed to just 1.
Some of them even have 3 words as their first name. How do I get it to keep
just the first word of the entire name from every row in this column?

Thank you in advance!!!

To just return the first word from a sequence, you could use this formula:

=LEFT(A1,IF(COUNTIF(A1,"* *")=0,255,FIND(" ",A1)-1))

OR

you could use the Data/Text to Columns wizard with <space> as the separator,
and retain the first column.
--ron

 

[Don Guillett]

Instead of a formula that eats up resources to re-calculate, this is a macro
that does it only once and leaves just the values. As written, it will leave
everything to the left of the FIRST space encountered.

 

[Don Guillett]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm