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?
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?
A mighty flame followeth a tiny sparkle!!
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
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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
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
A mighty flame followeth a tiny sparkle!!
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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
Little Bit Change. in Coding.Originally Posted by DRJ
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
Thanks all of u
i think it is enough for me & now i can resolve it my self
A mighty flame followeth a tiny sparkle!!
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]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Master you are right..Originally Posted by xld
I will keep it in mind.
Thanks...