Segregation of Numerical & Text data

[excelliot]

Hi
I have data in one column which is text as well as non text

Is it possibel to capture or identify only numerical data from the column & pasting it to new coloumn?

[Jacob Hilderbrand]

Try this macro.

Option Explicit
 
Sub SeparateTextAndNumbers()
Dim i               As Long
Dim j               As Long
Dim n               As Long
Dim LastRow         As Long
LastRow = Range("A65536").End(xlUp).Row
    For i = 1 To LastRow
        n = Len(Range("A" & i).Text)
        For j = 1 To n
            If IsNumeric(Mid(Range("A" & i).Text, j, 1)) Then
                Range("B" & i).Value = Range("B" & i).Text & Mid(Range("A" & i).Text, j, 1)
            Else
                Range("C" & i).Value = Range("C" & i).Text & Mid(Range("A" & i).Text, j, 1)
            End If
         Next j
    Next i
End Sub

[TonyJollans]

Jake's macro will completely separate numeric and non-numeric characters which might be what you want but if you, rather more simply, have some cells which are numeric and some cells which are text you can separate out the numeric ones by Copying and Paste Special > Add to an empty column.

[excelliot]

I think u missunderstood me
i have data in column like thiss

Col A

12.30
Basic
12.33
12.88
DIM
TAER


Now i wants to copy numeric data to adjacent column & Text data to different adjacent column.

like this

Col A-----Col B------ Col C

12.30----12.30
Basic -------------- Basic
12.33--- 12.33-----
12.88----12.88------
DIM ----------------DIM
TAER ---------------TAER

[TonyJollans]

As I suggested, copy column A and Paste Special > Add into column B - this will get the numbers. To get the text, in column C enter a formula such as =IF(B1="",A1,"") and if you want to just have the values then Copy Column C and Paste Special > Values to Column C.

You should be able to get code for this simply by recording yourself doing it.

[chandansify]

Try this macro.

Option Explicit
 
Sub SeparateTextAndNumbers()
Dim i As Long
Dim j As Long
Dim n As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
n = Len(Range("A" & i).Text)
For j = 1 To n
If IsNumeric(Mid(Range("A" & i).Text, j, 1)) Then
Range("B" & i).Value = Range("B" & i).Text & Mid(Range("A" & i).Text, j, 1)
Else
Range("C" & i).Value = Range("C" & i).Text & Mid(Range("A" & i).Text, j, 1)
End If
Next j
Next i
End Sub

Little Bit Change. in Coding.

Sub SeparateTextAndNumbers()
Dim i               As Long
    Dim j               As Long
    Dim n               As Long
    Dim LastRow         As Long
LastRow = Range("A65536").End(xlUp).Row
    For i = 1 To LastRow
            If IsNumeric(Range("A" & i).Text) Then
                Range("B" & i).Value = Range("A" & i).Text
            Else
                Range("C" & i).Value = Range("A" & i).Text
            End If
    Next i
End Sub

[excelliot]

Thanks all of u

i think it is enough for me & now i can resolve it my self

[Bob Phillips]

Guys,

I have to take issue with your use of the .Text property of the Range object.

Consider this case. A cell with the value 123. All agreed that is a number, no? Okay, so format it with a custrom formta of '0.00 "units"'. IsNumeric(Activecell.Value) returns True, whilst IsNumeric(Activecell.Text) returns False.

Thus, I feel you should test the Value property, and take the format over with it, to get the correct result.

Oh, and Rows.Count, not 65536.

[VBA]
Sub SeparateTextAndNumbers()
Dim i As Long
Dim j As Long
Dim n As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If IsNumeric(Range("A" & i).Value) Then
With Range("B" & i)
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
End With
Else
With Range("C" & i)
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
End With
End If
Next i

End Sub
[/VBA]

[chandansify]

Guys,

I have to take issue with your use of the .Text property of the Range object.

Consider this case. A cell with the value 123. All agreed that is a number, no? Okay, so format it with a custrom formta of '0.00 "units"'. IsNumeric(Actiuvecell.Value) returns True, whilst IsNumeric(Activecell.Text) returns False.

Thus, I feel you should test the Value property, and take the format over with it, to get the correct result.

Oh, and Rows.Count, not 65536.

[VBA]
Sub SeparateTextAndNumbers()
Dim i As Long
Dim j As Long
Dim n As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If IsNumeric(Range("A" & i).Value) Then
With Range("B" & i)
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
End With
Else
With Range("C" & i)
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
End With
End If
Next i

End Sub
[/VBA]

Master you are right..

I will keep it in mind.


Thanks...