Leave only numbers in a column

[sindhuja]

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]

[VBA]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[/VBA]

[Aussiebear]

What does this line do Bob?

RegEx.Pattern = "\d+"

[Bob Phillips]

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]

I thought the OP wanted all characters to be removed ....

[Aussiebear]

@ snb, The OP wants the numbers left.

@ Bob, Thanks for that

[Bob Phillips]

Oops, I think snb is right, I removed the wrong stuff.

[VBA]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
[/VBA]

[CodeNinja]

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

[sindhuja]

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]

[VBA]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[/VBA]

[sindhuja]

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