Sub ExtractText()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Dim cellValue As String
Dim dollarPos As Long
Dim spacePos As Long
Dim extractedText As String

' Set references to the source and target sheets
Set sourceSheet = ThisWorkbook.Sheets("SQL")
Set targetSheet = ThisWorkbook.Sheets("KEY")

' Find the last row with data in column A of the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Loop through each row in the source sheet
For i = 1 To lastRow
    cellValue = sourceSheet.Cells(i, 1).Value ' Get the value of the cell in column A
    
    ' Search for the dollar character in the cell value
    dollarPos = InStr(cellValue, "$")
    
    ' If dollar character is found, extract text until the next space character
    If dollarPos > 0 Then
        spacePos = InStr(dollarPos, cellValue, " ")
        If spacePos > 0 Then
            extractedText = Mid(cellValue, dollarPos + 1, (spacePos - 1) - dollarPos - 1)
            
            ' Remove special characters from the extracted text
            extractedText = Replace(Replace(extractedText, "$", ""), "_", "") ' Add more characters if needed
            
            ' Paste the extracted text to the target sheet
            targetSheet.Cells(j + 1, 1).Value = extractedText
            j = j + 1
        End If
    End If
Next i

End Sub

Sub ReplaceValues()
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Dim sourceRange As Range
Dim destRange As Range
Dim searchValue As Variant
Dim srcval As Variant
Dim replaceValue As Variant

' Set source and destination sheets
Set sourceSheet = ThisWorkbook.Sheets("Key")
Set destSheet = ThisWorkbook.Sheets("SQL")

' Find the last row in source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row

' Set source and destination ranges
Set sourceRange = sourceSheet.Range("B1:B" & lastRow)
Set destRange = destSheet.Range("A:A")

' Loop through each cell in column B of source sheet
For i = 0 To lastRow - 1
    ' Get the value to search for
    searchValue = sourceSheet.Cells(i + 1, "B").Value
  
    ' Find the matching value in column A of source sheet
    'replaceValue = Application.VLookup(searchValue, destRange, 1, False)
   ' MsgBox "The value of i is " & replaceValue, vbInformation
    ' If a match is found, replace the value in column A of source sheet
    'If Not IsError(replaceValue) Then
     '   destSheet.Cells(0, 0).Value = replaceValue
          
    'End If
   destRange.Replace What:=sourceSheet.Cells(i + 1, "A").Value, Replacement:=searchValue
    
Next i

MsgBox "Values replaced successfully!", vbInformation

End Sub

Search and replace the dollar symbol in replace part