Sorter Formula

[Rubber Beaked Woodpecker]

The following formula works very well but just curious if there is an easier/shorter way of writing the formula?

=IF(AND(LEFT(A65,1)="1",(RIGHT(A65,1)<>"t")),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A65,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"/",""),"")

Thanks

RBW

 

[kevin9999]

Personally, I would use a UDF. Something like this:

Function RBW(r As Range) As String
    Dim i As Long, s As String, a
    a = Array("1", "2", "3", "4", "5", "6", "7", "8", "9")
    If Left(r, 1) = 1 And Right(r, 1) <> "t" Then
        s = r.Value
        For i = LBound(a) To UBound(a)
            s = Replace(s, a(i), "")
        Next
        RBW = Trim(s)
    Else
        RBW = r
    End If
End Function

Then the formula becomes simpler to enter:

Book1
	A	B
1	13 leave 1111	leave
2	leave 123 123	leave 123 123
3	123 leave	leave
4	1 321 312 321 312
5	321 321 321 t	321 321 321 t
6	1 999 111 ttt 999	ttt
7	123456789
8	123456789 t	123456789 t
Sheet1

Cell Formulas
Range	Formula
B1:B8	B1	=RBW(A1)