Remove last letter from entries in a column

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
Sun May 27 17:20:47 CDT 2007

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
news:OBSSvkKoHHA.4772@TK2MSFTNGP06.phx.gbl...
>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!
>

TxWebdesigner
Sun May 27 18:45:44 CDT 2007

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!

"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:eJvnN1KoHHA.3704@TK2MSFTNGP02.phx.gbl...
> 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
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
> news:OBSSvkKoHHA.4772@TK2MSFTNGP06.phx.gbl...
>>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!
>>
>

Ron
Sun May 27 19:49:28 CDT 2007

On Sun, 27 May 2007 16:51:22 -0500, "TxWebdesigner"
<beverly@beverlylanedesigns.com> wrote:

>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
Sun May 27 20:31:56 CDT 2007

Here's another one.

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

Biff

"TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
news:OBSSvkKoHHA.4772@TK2MSFTNGP06.phx.gbl...
>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!
>

Dave
Sun May 27 20:48:39 CDT 2007

One based on Biff's idea:

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



"T. Valko" wrote:
>
> Here's another one.
>
> =IF(ISNUMBER(SEARCH(" ?xx",A1&"xx")),LEFT(A1,LEN(A1)-2),A1)
>
> Biff
>
> "TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
> news:OBSSvkKoHHA.4772@TK2MSFTNGP06.phx.gbl...
> >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!
> >

--

Dave Peterson

TxWebdesigner
Sun May 27 22:35:11 CDT 2007

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" <ronrosenfeld@nospam.org> wrote in message
news:nl9k53lpv7g46kal75obno0ls772km6pvn@4ax.com...
> On Sun, 27 May 2007 16:51:22 -0500, "TxWebdesigner"
> <beverly@beverlylanedesigns.com> wrote:
>
>>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

Ron
Mon May 28 06:38:55 CDT 2007

On Sun, 27 May 2007 22:35:11 -0500, "TxWebdesigner"
<beverly@beverlylanedesigns.com> wrote:

>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
Mon May 28 07:11:49 CDT 2007

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
SalesAid Software
dguillett1@austin.rr.com
"TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
news:eGi7okLoHHA.1388@TK2MSFTNGP05.phx.gbl...
> 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!
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:eJvnN1KoHHA.3704@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
>> news:OBSSvkKoHHA.4772@TK2MSFTNGP06.phx.gbl...
>>>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
Mon May 28 07:25:55 CDT 2007

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


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:e3LglFSoHHA.4124@TK2MSFTNGP02.phx.gbl...
> 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
> SalesAid Software
> dguillett1@austin.rr.com
> "TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
> news:eGi7okLoHHA.1388@TK2MSFTNGP05.phx.gbl...
>> 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!
>>
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
>> news:eJvnN1KoHHA.3704@TK2MSFTNGP02.phx.gbl...
>>> 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
>>>
>>> --
>>> Don Guillett
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> "TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message
>>> news:OBSSvkKoHHA.4772@TK2MSFTNGP06.phx.gbl...
>>>>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!
>>>>
>>>
>>
>>
>