Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Houston, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove last character in cell

    Good afternoon, we are working in a very large worksheet and in one column there are email addresses in each cell, sometimes close to 100 emails per cell. Email addresses are separated by semicolons. Sometimes, however, there is an extra hanging semicolon as the last characeter in the cell.

    Is there a formula that we could apply to remove hanging semicolons that appear as the last character in a cell? Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like:
    =IF(RIGHT(A1,1)=";",LEFT(A1,LEN(A1)-1),A1)

    Replace A1 with the cell with the email address

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Sarg,

    The formula in the above post will remove the semicolon but it will also place the revised value in another cell leaving you with the chore of copying the new value back to the original cell and deleting the contents of the cell with the revised value.

    Here is a VBA approach that just removes all the trailing semicolons. Place in a standard module. Adapting to your project is as easy as replacing the column number and start row number in the code with the correct values where indicated. (Before run code and after run code images below)

    [/I]HTH,
    Maud

    SemiColons1.png SemiColons2.png

    Code:
    Sub DelSemiColon()
    '-------------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim rng As Range
    Col = 1  'CHANGE TO THE COLUMN WITH THE EMAILS
    StartRow = 1  'CHANGE TO THE FIRST ROW THAT HAS THE EMAILS
    LastRow = ActiveSheet.Cells(Rows.Count, Col).End(xlUp).Row
    Set rng = Range(Cells(StartRow, Col), Cells(LastRow, Col))
    '------------------------------------------------
    'LOOK FOR CELLS WITH TRAILING SEMICOLONS AND REMOVE
    For Each cell In rng
        If Right(cell.Value, 1) = ";" Then
            cell.Value = Left(cell.Value, Len(cell.Value) - 1)
        End If
    Next cell
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-17 at 20:33. Reason: revised code with variables

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Houston, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much...this worked perfectly!

Posting Permissions

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