[Closed] Excel Help

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

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

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

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.

Formula along the lines of...

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

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).

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

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

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

Why not just use find and replace?

Why not just use find and replace?

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

what Doug said