need to merge the price from E column to D column as like 21187 row ,

Question

there are 8k rows are there in the database need to separate the price and insert into D column.please do check the screenshot is there any formula ??

check the screenshot

Answer 1

Need more examples but you can use an UDF in column D, or loop column E occupied cells and apply the function to the column E values, and output result into column D. There are lots of assumptions in the below, including that you want the first match only. It applies a regex (search pattern applied to the cell text) that looks for qualifying cost data as starting with Rs., then 1 or more numbers, then optional "." and optional more numbers.

---

VBA:

Option Explicit
Public Sub TransferCosts()
    Dim arr(), i As Long
    With Worksheets("Sheet1")
        arr = .Range("E1:E14").Value
        ReDim Preserve arr(1 To UBound(arr, 1), 1 To UBound(arr, 1))
        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 2) = arr(i, 1)
            arr(i, 1) = GetCost(arr(i, 1))
        Next i
       .Range("D1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    End With
End Sub

Public Function GetCost(ByVal inputString As String) As String
    Dim arr() As String, i As Long, matches As Object, re As Object
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\bRs\.\d+(\.[^\s]\d+)?\b"
        If .test(inputString) Then
            GetCost = .Execute(inputString)(0)
        Else
            GetCost = vbNullString
        End If
    End With
End Function

---

Regex:

---

Full explanation:

Pattern:

/
\bRs\.\d+(\.[^\s]\d+)?\b
/
gm

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

Rs matches the characters Rs literally (case sensitive)

\. matches the character . literally (case sensitive)

\d+ matches a digit (equal to [0-9])

"+" Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

1st Capturing Group (\.[^\s]\d+)?

? Quantifier — Matches between zero and one times, as many times as possible, giving back as needed (greedy)

\. matches the character . literally (case sensitive)

Match a single character not present in the list below [^\s]

\s matches any whitespace character (equal to [\r\n\t\f\v ])

\d+ matches a digit (equal to [0-9])

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

Try it

---

References:

Some great YouTube videos on regular expressions by Shiffman at The Coding Train