Extract Data Contains Mark etc...As Number

[muhammad susanto]

hi all..

how to extract data below (contains mark etc.)

Book1
	K	L
3	data (text)	target (number)
4	= 1.812,00 m3 x 610.383,00 /m3 x 0,840	1,812.00
5	= 579,00 m3 x 610.383,00 /m3 x 0,840	579.00
6	= 20,00 m3 x 610.383,00 /m3 x 0,840	20.00
7	= 24.500,00 m3 x 610.383,00 /m3 x 0,840	24,500.00
8	= 356.500,00 m3 x 610.383,00 /m3 x 0,840	356,500.00
9	= 1.750.500,00 m3 x 610.383,00 /m3 x 0,840	1,750,500.00
Sheet1

my target in cell L4 down and as number not text

any help would greatly appreciated..

.sst

 

[mohadin]

Well
Have you noticed that the above code is for testing
The it process the data in column A starting A1down
If you can not amend it to column K (K4 down) let me know will do it for you
Deal?

 

[mohadin]

Tell you what
For your data in #! Data in K ,Result in L
Try this version

Sub test()
    Dim lr, i, m
    lr = Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, 11).End(xlUp).Row
    With CreateObject("VBScript.RegExp")
        .Global = True
        For i = 1 To lr - 3
            .Pattern = "(\d.+?\d+? {1,10})"
            Set m = .Execute(Cells(i + 3, 11))
            Cells(i + 3, 11).Offset(, 1) = m(0)
            .Pattern = "\."
            Cells(i + 3, 11).Offset(, 1) = .Replace(m(0), "")
            .Pattern = "\,"
            Cells(i + 3, 1).Offset(, 1) = Format(.Replace(Cells(i, 1).Offset(, 1), "."), "#,##00.00")
        Next
    End With
End Sub

 

[mohadin]

Ok!
Forget #12
this is the trimmed version
Hope you like it
Again data in K4& down

Sub test2()
    Dim i As Double
    Dim m, Rx As Object
    Set Rx = CreateObject("VBScript.RegExp")
    Rx.Global = True
    For i = 1 To Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, 11).End(xlUp).Row - 3
        Rx.Pattern = "(\d.+?\d+? {1,10})"
        Set m = Rx.Execute(Cells(i + 3, 11))
        With Cells(i + 3, 11).Offset(, 1)
            .Value = m(0)
            Rx.Pattern = "\."
            .Value = Rx.Replace(.Value, "")
            Rx.Pattern = "(\,)"
            .Value = Rx.Replace(.Value, ".")
        End With
    Next
End Sub

 

[muhammad susanto]

hi mohadin, thank you so much, bil khoir.