PDA

View Full Version : Leave only numbers in a column



sindhuja
06-30-2012, 11:40 PM
Hello…

I want to remove all the text in a column and leave only the numbers. Ex - No of cases - 9 and the result should be only 9 in a cell. This way for cells in a column. Like this i need to format many columns.And if it is No of cases - -9 then the result should be -9

Also we have a column with values start hours - 9, start mins -5, end hours - 15, and end mins - 35 i want to delete all the text in the cell and the result should be in the format HH:MM in this case 09:05 and 15:35 separately with the column named start time and endtime.

since i have too many rows and columns to work with in many excel macro will really help me.

-Sindhuja

Bob Phillips
07-01-2012, 10:25 AM
Public Sub ProcessData()
Dim RegEx As Object
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "\d+"

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow 'Lastrow to 1 Step -1

Cells(i, "A").Value = RegEx.Replace(Cells(i, "A").Value, "")
Next i
End With

Application.ScreenUpdating = True
End Sub

Aussiebear
07-01-2012, 03:01 PM
What does this line do Bob?

RegEx.Pattern = "\d+"

Bob Phillips
07-02-2012, 12:26 AM
It sets the search pattern to all numeric characters, \d is numbers, + is all of them. The replace later then replaces them with blank.

snb
07-02-2012, 12:41 AM
I thought the OP wanted all characters to be removed ....

Aussiebear
07-02-2012, 12:47 AM
@ snb, The OP wants the numbers left.

@ Bob, Thanks for that

Bob Phillips
07-02-2012, 01:50 AM
Oops, I think snb is right, I removed the wrong stuff.

Public Sub ProcessData()
Dim RegEx As Object
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[^\d]+"
RegEx.Global = True

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow 'Lastrow to 1 Step -1

.Cells(i, "A").Value = RegEx.Replace(.Cells(i, "A").Value, "")
Next i
End With

Application.ScreenUpdating = True
End Sub

CodeNinja
07-02-2012, 06:48 AM
xld,
Absolutely brilliant using regex here. I have never used this extremely powerful tool, and your post got me started on it. I will be spending the next few days learning this, so thank you very much.

One question... I think Sindhuja wanted a possible negative number, so shouldn't the regex pattern be:

RegEx.Pattern = "[^-?\d]+"


Thanks again for teaching me :super:

sindhuja
07-02-2012, 07:08 AM
Hi,

Thank you everyone for the coding

I need to remove the - sign also. If the value for example is "Amount - -12" which is negative value. Then the "Amount -" to be removed leaving -12 in the cell as value.

Also is it possible to use an array ( as I have text from multiple colums to be deleted in the same way)

sindhuja

snb
07-02-2012, 08:59 AM
Sub snb()
sn = Columns(1).SpecialCells(2)

With CreateObject("VBScript.RegExp")
.Pattern = "\D+ "
For j = 1 To UBound(sn)
sn(j, 1) = .Replace(sn(j, 1), "")
Next
End With

Columns(1).SpecialCells(2).Offset(, 3) = sn
End Sub

sindhuja
07-03-2012, 08:49 PM
sorry to bother you again. Can you explain the coding as am new to usage of patterns in coding.

Also i need to delete the text only in the specific columns not all the columns. Can the usage the array (specifying the columns) will solve my problem. if so, assist me with that as well.

-sindhuja