Separate Text & Numbers

Thank you for your UDF.

Pl provide this as add-in it will useful to install for permanent.

Hi

You can save file as .xla & its done.

Also file is attached herewith.

Thanks//VaibhavJ

Thank you Vaibhav ji,

Vaibhav ji,

pl see below post, any solution is there ?

/forum/how-to-print-each-character-in-separate-cell-199007.asp#.UnD6WnBgf74

dear Vaibhav Ji,

please find below UDF formula for finding a missing numbers in a sequnce in column;

syntax:misslist(a1:a50), it is showing in each column , but I want all missing numbers in one column only with "," separated.

please develop sir.

Function MissList(Rng As Range)
   '  list of Missing Numbers
   Dim Arr()
   Dim i As Long
   Dim n As Long
   With WorksheetFunction
      For i = .Min(Rng) To .Max(Rng)
         If .CountIf(Rng, i) = 0 Then
             n = n + 1
             ReDim Preserve Arr(1 To n)
             Arr(n) = i
         End If
      Next
      MissList = .Transpose(Arr)
   End With
End Function
'---------------

pl attach sample file..

Dear Sir,

please find the sample file.

Thanks in advance

try this

Function MissList(Rng As Range) As String
   Dim X As Long, MaxNum As Long
   MaxNum = WorksheetFunction.Max(Rng)
   ReDim Nums(1 To MaxNum)
   For X = 1 To MaxNum
     If Rng.Find(X, LookAt:=xlWhole) Is Nothing Then
       MissList = MissList & ", " & X
     End If
   Next
   MissList = Mid(MissList, 3)
 End Function

Thx

Pl find attachment

Also find addins!

Dear Sir,

Thanks for your speed responce..

but, the said function not meeting my requirement, pl see enclsoed file,

and advise why it is coming like that.

Ok, Solveed it!

Dear Vaibhav,

Sorry to say that,

I checked the excel sheet, 1536 is in 14th row, but it is showing as missing no, i deleted some other nos, but there is no change in the answer.

pl check and solve the problem sir.

Hmnn..some minor error!

Try this, i checked & working at my end.