Hi all,

original text in cell c4:
Out of total dues of Rs.855.8k bills for INR 651.2k were rebilled and
resubmitted for payment and the balance of Rs 204,600 are under process.
required text in cell d4:
Out of total dues of INR 855.8k (SGD 25,365) bills for INR 651.2k (SGD
19,308) were rebilled and resubmitted for payment and the balance of INR
204.6k (SGD 6,066) are under process.

that is, multiply by 1000 and divide by 33.7272 (value at d1 cell), there
could be more numbers in the same cell. Except numbers all the other text
should be same in d4. Third numbe is very typical, pl note.
the number will be prefix with "Rs.","INR","Rs ", suffix will be "k" or no "k"

these type of changing to be done for some 200 to 300 rows, all the time.
Can any one help me.

Try this user-defined function:

Option Explicit

Public Function AddConv(Stmt As Range, ConvRate As Range) As String
Dim x As Long, y As Integer, z As Integer, a As Integer
Dim nbr As Double, TheRate As Double
Dim str1 As String, str2 As String, str3 As String
AddConv = vbNullString
TheRate = ConvRate.Value
On Error GoTo ACerr
If Stmt.Cells.Count 1 Then
AddConv = "ERROR"
Exit Function
End If
For x = 1 To (Len(Stmt) - 2)
str1 = Mid(Stmt, x, 3)
Select Case str1
Case "INR", "Rs.", "Rs "
a = 0
str3 = vbNullString
AddConv = AddConv & str1
str2 = FindNbr(x + 3, Stmt)
If str1 = "INR" Then
AddConv = AddConv & " "
y = 4
a = 1
Else
y = 3
End If
If Mid(Stmt, x + Len(str2) + y, 1) = "k" Then
z = 1000
AddConv = AddConv & str2 & "k "
a = a + 4
ElseIf Mid(Stmt, x + Len(str2) + y, 1) = " " Then
str3 = Application.WorksheetFunction.Text(CDbl(str2) / 1000,
"#,##0.0")
AddConv = AddConv & str3 & "k "
z = 1
str3 = vbNullString
a = a + 3
End If
nbr = (CDbl(str2) * z) / TheRate
str3 = str3 & "(SGD " & Application.WorksheetFunction.Text(nbr,
"#,##0") & ") "
AddConv = AddConv & str3
x = x + Len(str2) + a
Case Else
AddConv = AddConv & Mid(Stmt, x, 1)
End Select
Next x
AddConv = AddConv & Mid(Stmt, x, 3)
Exit Function
ACerr:
AddConv = "ERROR"
End Function

Private Function FindNbr(StartPos As Long, Stmt As Range) As String
Dim str4 As String, n As Long
FindNbr = vbNullString
n = StartPos
Do While n < Len(Stmt)
Select Case Mid(Stmt, n, 1)
Case "0" To "9", ",", "."
str4 = str4 & Mid(Stmt, n, 1)
Case " "
'do nothing
Case Else
FindNbr = str4
Exit Function
End Select
n = n + 1
Loop
FindNbr = "No Number"
End Function

You would call this in D4 like this:
=AddConv(C4,$D$1)

Put the code in a general VBA module in your workbook. If you are new to
user-defined functions (macros), this link to Jon Peltier's site may be
helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"Eddy Stan" wrote:

Hi all,

original text in cell c4:
Out of total dues of Rs.855.8k bills for INR 651.2k were rebilled and
resubmitted for payment and the balance of Rs 204,600 are under process.
required text in cell d4:
Out of total dues of INR 855.8k (SGD 25,365) bills for INR 651.2k (SGD
19,308) were rebilled and resubmitted for payment and the balance of INR
204.6k (SGD 6,066) are under process.

that is, multiply by 1000 and divide by 33.7272 (value at d1 cell), there
could be more numbers in the same cell. Except numbers all the other text
should be same in d4. Third numbe is very typical, pl note.
the number will be prefix with "Rs.","INR","Rs ", suffix will be "k" or no "k"

these type of changing to be done for some 200 to 300 rows, all the time.
Can any one help me.