need only data

[sksanjeev786]

Hi Team,

I have data in various formate. can I get any formula to find only data. There will be % space on random data but I need only data i.e 32 data can be in single digit as well. (instead of 32 it can be 8 with all below condtion)

32%A, (%)
32%ab,
32AB, (No Space )
32 AB(only has Space)

Thanks in Advance.
Regards,
Sanjeev

 

[sandy666]

You are most welcome

 

[sksanjeev786]

Hi
Try This VBA Code in a module
Data start from A2 down

Sub Test()
    Dim lr, i
    Dim Rg, m As Object
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
        For i = 1 To lr
            If Rg.Test(Cells(i, 1)) Then
                Set m = Rg.Execute(Cells(i, 1))
                Cells(i, 1).Offset(, 1) = m(0)
            End If
        Next
    End With
End Sub

Hi Mohdin,

Thank you for the VB and I have tried with your macro
and for 23% I am getting a 0 score and rest data I am getting in-text formate as I need in value formate.

thank you
Regards,
Sanjeev

 

[mohadin]

Try this to get values
about the 23 I don't understand
Can you post wher you get the 0

Sub Test()
    Dim lr, i
    Dim Rg, m As Object
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
        For i = 1 To lr
            If Rg.Test(Cells(i, 1)) Then
                Set m = Rg.Execute(Cells(i, 1))
                Cells(i, 1).Offset(, 1) = m(0)*1
            End If
        Next
    End With
End Sub

 

[sksanjeev786]

Try this to get values
about the 23 I don't understand
Can you post wher you get the 0

Sub Test()
    Dim lr, i
    Dim Rg, m As Object
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
        For i = 1 To lr
            If Rg.Test(Cells(i, 1)) Then
                Set m = Rg.Execute(Cells(i, 1))
                Cells(i, 1).Offset(, 1) = m(0)*1
            End If
        Next
    End With
End Sub

now it look great Mohadin. just wanted to check can we give any function in macro as my source is always not in A2 column it can be in any column
so can I give any function so that I can take data from any source.


Thank you.
Sanjeev

 

[mohadin]

Here you go

Function GDATA(r As Range)
    Dim Rg As Object
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
            If Rg.Test(r) Then
                GDATA = Rg.Execute(r)(0) * 1
            End If
End Function

=GDATA(B2)

 

[sksanjeev786]

Here you go

Function GDATA(r As Range)
    Dim Rg As Object
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
            If Rg.Test(r) Then
                GDATA = Rg.Execute(r)(0) * 1
            End If
End Function

=GDATA(B2)

it work but for 32% i am getting 0

 

[mohadin]

Sub Test2()
    Dim lr, i
    Dim Rg, m As Object
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
        For i = 1 To lr
            If Rg.Test(Cells(i, 1).Value) Then
                Set m = Rg.Execute(Trim(Cells(i, 1)))
                If m.Count = 1 Then
                    Cells(i, 1).Offset(, 1) = m(0) * 1
                Else
                    Cells(i, 1).Offset(, 1) = m(1) * 1
                End If: End If
        Next
    End With
End Sub

 

[sksanjeev786]

Sub Test2()
    Dim lr, i
    Dim Rg, m As Object
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
        For i = 1 To lr
            If Rg.Test(Cells(i, 1).Value) Then
                Set m = Rg.Execute(Trim(Cells(i, 1)))
                If m.Count = 1 Then
                    Cells(i, 1).Offset(, 1) = m(0) * 1
                Else
                    Cells(i, 1).Offset(, 1) = m(1) * 1
                End If: End If
        Next
    End With
End Sub

look great !!! Thanks Mohadin for your help
I will add the above code with the previous code you have provided macro in GData

Regards,
sanjeev

 

[mohadin]

Function GDATA(r As Range)
    Dim Rg, m As Object
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
            If Rg.Test(r) Then
                Set m = Rg.Execute(r)
                If m.Count = 1 Then
                    GDATA = m(0) * 1
                Else
                    GDATA = m(1) * 1
                End If: End If
End Function

 

[mohadin]

Function GDATA(r As Range)
    Dim Rg, m As Object
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
            If Rg.Test(r) Then
                Set m = Rg.Execute(r)
                If m.Count = 1 Then
                    GDATA = m(0) * 1
                Else
                    GDATA = m(1) * 1
                End If: End If
End Function