String with text and numbers to ROMAN

[fcohen]

Hi,

I need a formula to replace numbers within a strin to Roman:

My18Example22Is

would become

MyXVIIIExampleXXIIIs

 

[John_w]

Try this UDF:

Public Function CvtRoman(text As String) As String

    Dim re As Object
    Dim matches As Object
    Dim i As Long, p As Long
    
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Pattern = "\d+"
        .Global = True
        Set matches = .Execute(text)
    End With
    
    CvtRoman = ""
    p = 0
    For i = 0 To matches.Count - 1
        CvtRoman = CvtRoman & Mid(text, p + 1, matches(i).FirstIndex - p) & Application.WorksheetFunction.Roman(matches(i).Value)
        p = matches(i).FirstIndex + matches(i).Length
    Next
    If p < Len(text) Then CvtRoman = CvtRoman & Mid(text, p + 1)
    
End Function

 

[Rick Rothstein]

Here is another UDF you can try...

[table="width: 500"]
[tr]
	[td]Function Romanize(S As String) As String
  Dim X As Long, Num As String
  Romanize = "X" & S
  For X = Len(Romanize) To 2 Step -1
    If Mid(Romanize, X - 1, 2) Like "[!0-9]#" Then
      Num = Val(Mid(Romanize, X))
      Romanize = Application.Replace(Romanize, X, Len(Num), Application.Roman(Num))
    End If
  Next
  Romanize = Mid(Romanize, 2)
End Function[/td]
[/tr]
[/table]

 

[John_w]

Nice one Rick. I always overlook VBA's Like operator.