Forum Discussion
removing unwanted data from a cell
I have a column with codes, the codes I need are FT and FC, but there are cells with extra codes which I want to remove and only have FT or FC appear in the cell. how do I do this without having to click on each cell and remove the unwanted data? example attached
Assuming FT and FC are always at the end, select column (or range) with codes, Ctrl+H and replace all *FT on FT. Same for FC.
- I don't think there is any built in functionality that will remove those other code from those cells. You could write a macro to do it but I would recommend, instead, to use a helper column with a formula (and if you want you could copy and 'paste values' over the original column or better yet leave that column and just 'hide' it). Here is a possible formula:
=TRIM(IF(ISNUMBER(SEARCH("FT",C1:C100)),"FT","")&" "&IF(ISNUMBER(SEARCH("FC",C1:C100)),"FC","")) Assuming FT and FC are always at the end, select column (or range) with codes, Ctrl+H and replace all *FT on FT. Same for FC.
- worked like a charm! thanks so much
- well assuming FT and FC are the only 'F' codes you could just use *F
the only other caveot is that if you have FT and FC in the same cell this method will remove one of them (the one that is not last) and will not show that both were there
