Results 1 to 7 of 7
  1. #1
    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. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You are almost there

    Try this

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



    MTB

  3. #3
    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. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #5
    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

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    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
  •