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.
9 Replies
You already have what you require from SergeiBaklan​ but, as an alternative, for better or worse, I tend to leave source data unaltered. A 365 formula that filters out records with codes other than FC or FT might be
= FILTER(table, REGEXTEST(codes, "\b(FC|FT)\b"))where 'table' and 'codes' are defined names.
=REGEXEXTRACT(codes, "\b(FC|FT)\b")
I don't think there is any built in functionality that will remove those other code from those cells.
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
joannduplessis88 , you are welcome
- 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",""))
