Sorting arbitrary numeric data and leave whitespaces for the missing values

I need to sort the numeric data in a row/column while leaving space of missing values at scale of 1-5.

Like:

A        B  
13245    1_2_3_4_5  
152....  1_2_ _ _5

Answers


Try this UDF:

Function ModSort(Str As String) As String
    Res = ""
    For Iter = 1 To 5
        If InStr(1, Str, CStr(Iter)) Then
            Hold = CStr(Iter) & "_"
        Else
            Hold = " _"
        End If
        Res = Res & Hold
    Next Iter
    Res = Left(Res, 9)
    ModSort = Res
End Function

Screenshot:

Let us know if this helps.


Not meant as a serious answer (UDF seems good enough) but appears possible with a formula:

=IF(LEN(SUBSTITUTE(A2,1,""))<LEN(A2),"1_"," _")  
&IF(LEN(SUBSTITUTE(A2,2,""))<LEN(A2),"2_"," _")  
&IF(LEN(SUBSTITUTE(A2,3,""))<LEN(A2),"3_"," _")  
&IF(LEN(SUBSTITUTE(A2,4,""))<LEN(A2),"4_"," _")  
&IF(LEN(SUBSTITUTE(A2,5,""))<LEN(A2),"5",)

Need Your Help

Button background condition

android background condition

how can I create a condition if a button has set a backround and set another?

Make website login work on WordPress too

php mysql wordpress authentication

I developed a website using PHP and MySQL, which already has a login and registration form. (myweb.com)