PDA

View Full Version : [SOLVED] Segregation of Numerical & Text data



excelliot
07-11-2005, 11:14 PM
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? :friends:

Jacob Hilderbrand
07-11-2005, 11:31 PM
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
07-11-2005, 11:44 PM
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
07-12-2005, 01:22 AM
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


:beerchug:

TonyJollans
07-12-2005, 02:31 AM
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
07-12-2005, 02:40 AM
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
07-12-2005, 03:28 AM
Thanks all of u

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

Bob Phillips
07-12-2005, 03:29 AM
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.


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

chandansify
07-12-2005, 05:17 AM
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.


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


Master you are right..

I will keep it in mind.


Thanks...