# Thread: Separate last character in Column

1. Registered User
Join Date
Jun 2005
Posts
45

## Unanswered: Separate last character in Column

Hello,

I am trying to help someone, and the question i have is this. For example,

i have in A1, A2, A3

MarkW
JohnS
WilliamG

I would like to separate the last initial from each word, so it would be like

Mark W
John S
William G

===============

I am new to these formulas, but

i tried this formula =LEFT(A1,LEN(A1)-1)

It takes Mark and puts it in another column

RIGHT(G1,1) would take the Last initial.

Basically my problem is that the above formulas would put them in separte columns, I was wondering if there is a good way to do this, so that the original column is modified, without having new columns for name and initial.

Mark W
John S
William G

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

You are almost there

Try this

=LEFT(A1,LEN(A1)-1) & " " & RIGHT(A1,1)

MTB

3. Registered User
Join Date
Jan 2006
Posts
23
It only works for the first name A1 only. Is there a way to it for all the users A1 through A600?
Thanks

4. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

I think you should be a little more explicit, but if, for instance, you want the results in cells C1 through C600, then put the formula in C1 and copy it down into all rows to row 600.

If you want something different then ...!!??

MTB

5. Registered User
Join Date
Jan 2006
Posts
23
mmm, ok. well, let me ask you this I can just get rid of the last initial al together in all of the users.
from Helfrich A
to Helfrich ----just plain last name.
Thanks

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Can't Put a Formula in top of it seft

What I would do is

place this mikethebike formula LEFT(A1,LEN(A1)-1) & " " & RIGHT(A1,1)
into b1
then Copy it down the 600 rows

Then copy the all of Col b to A but do a Paste special values back to col a

7. Registered User
Join Date
Jun 2005
Posts
45
Thank You !!!!!!!! myle, MikeTheBike, arandomfrog

MikeTheBike put the two formulas together and Myle gave me a really good idea on how to manipulate the original column.

arandomfrong, thanks for your question, i actually wrote a formula that will put only last name, you can get rid of the last initial with this.

=LEFT(A1,LEN(A1)-1)

Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•