Viewing 13 posts - 1 through 13 (of 13 total)
  • Excel Help
  • Hobster
    Free Member

    Need to remove a trailing semicolon from cells of varying lengths.

    It will not always be present.

    So would only need to be removed on the bottom of these two rows

    Account group default (all); BI001816 D; XPTUAG557
    Account group default (all); XXXY (XXXY123);

    Any suggestions on the easiest way.

    Thanks

    jam-bo
    Full Member

    data > text to columns. use semi-colon as a delimiter.

    Hobster
    Free Member

    Did think about that but number of semi colons in cell varies

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    Cougar
    Full Member

    Won’t that explode if there’s other semi-colons elsewhere in the cell?

    whitestone
    Free Member

    Export as CSV but use a character that doesn’t appear in the cells as the separator, lets assume it’s the comma.
    Run sed “s/;,/,/” filename.csv > new_filename.csv
    Import file back into Excel.

    grizedaleforest
    Full Member

    Formula along the lines of…

    =REPLACE(A1,LEN(A1),RIGHT(A1)=”;”,””)

    jimdubleyou
    Full Member

    Define existing data as a table.

    Create a new table in another sheet with the source as an SQL query.

    Query is:

    Select other fields ,
    replace(old_table_element,”;”,” “), other fields from old_table

    Edit: or just use the replace function in Excel (didn’t know that existed).

    DougD
    Full Member

    =IF(RIGHT(A1,1)=”;”,(LEFT(A1,LEN(A1)-1)),A1)

    footflaps
    Full Member

    Hit Alt+F11

    Click on Sheet1(Sheet1) and in RHS window type

    Option Explicit

    Public Sub CleanCells()

    Dim Row As Integer
    For Row = Sheet1.UsedRange.Rows(1).Row To Sheet1.UsedRange.Rows(1).Row + Sheet1.UsedRange.Rows.Count

    Dim Cell As Range
    For Each Cell In UsedRange.Rows(Row).Cells

    If Right(Cells(Row, Cell.Column), 1) = “;” Then
    Cells(Row, Cell.Column) = Left(Cells(Row, Cell.Column), Len(Cells(Row, Cell.Column)) – 1)
    End If

    Next Cell

    Next Row

    MsgBox (“All done”)

    End Sub

    Hit CTRL+G

    In the Immediate box at the bottom, type

    call sheet1.CleanCells

    It will remove any trailing semi-colons from all cells in Sheet1

    Hobster
    Free Member

    Formula along the lines of…

    =REPLACE(A1,LEN(A1),RIGHT(A1)=”;”,””)

    Works but there appear to be random trailing spaces which throw it out.

    Will use TRIM to tidy it up.

    Thanks all

    mugsys_m8
    Full Member

    Why not just use find and replace?

    footflaps
    Full Member

    Why not just use find and replace?

    Can’t specify only trailing characters (that I know of). I just use VBA for everything.

    reggiegasket
    Free Member

    what Doug said

Viewing 13 posts - 1 through 13 (of 13 total)

The topic ‘Excel Help’ is closed to new replies.