Ad Free CAclubindia and Daily Updates on your Mobile. Subscribe Now!

Separate Text & Numbers

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)

28 October 2013

Using UDF (user defined function) to separate Text & Number from excel cell.

Syntex is :

=GetNum(A1) for separating Number

=GetTxt(A1) for separating Text.

Here A1 refers to cell which is holding data. Please refer attached file.

Thanks//VaibhavJ

Reply
Follow
Share

25 Replies

satya (Executive Excise) (1839 Points)
Replied 30 October 2013

Thank you for your UDF.

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

Upgrade to CAclubindia Pro. Subscribe Now!

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)
Replied 30 October 2013

You can save file as .xla & its done.

Also file is attached herewith.

Thanks//VaibhavJ

Attached File : 112266 1244569 separate text.xla downloaded: 241 times

satya (Executive Excise) (1839 Points)
Replied 30 October 2013

Thank you Vaibhav ji,

satya (Executive Excise) (1839 Points)
Replied 30 October 2013

Vaibhav ji,

pl see below post, any solution is there ?

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

satya (Executive Excise) (1839 Points)
Replied 30 January 2014

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
'---------------

Upgrade to CAclubindia Pro. Subscribe Now!

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)
Replied 30 January 2014

pl attach sample file..

satya (Executive Excise) (1839 Points)
Replied 30 January 2014

Dear Sir,

please find the sample file.

Thanks in advance

Attached File : 42215 1279510 sample file.xlsx downloaded: 303 times

1 Like

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)
Replied 30 January 2014

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