Remove everything before specific character if found in Excel

1
votes

I want to remove everything before the colon, so that only the names are left. My current formula does that but it puts a #!Value error if there is no colon. It should change nothing and just copy the exact name if no ":" is present.

Column C 

        key1:john
        key1:mike
        key1:edmund
        hello
        key3:edmund

etc

My formula =IFERROR(RIGHT(C1,LEN(C1)-FIND(":",C1)),"")

Column D 

        john
        mike
        edmund
       #!Value
        edmund

etc
3
excelexcel-formula

3 Answers

2
votes

Your formula nearly does it, with just this one change: 

=IFERROR(RIGHT(C1,LEN(C1)-FIND(":",C1)), C1) 
                                          ^----- replaced "" with C1

This formula gives the results: 

john
mike
edmund
hello
edmund
0
votes

Consider:

=IF(ISERROR(FIND(":",A1)),A1,MID(A1,FIND(":",A1)+1,9999))
0
votes

This is my solution to the problem:

=IF(ISNUMBER(SEARCH(":",C1)),RIGHT(C1,LEN(C1)-FIND(":",C1)),C1)