Extract Data Only Roman Number

[muhammad susanto]

hi all...

how to extract data contains roman number like this :

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>data</th><th>target</th></tr></thead><tbody>
<tr><td>Rumah Negara Golongan I Permanen</td><td>I</td></tr>
<tr><td>Rumah Negara Golongan II Permanen</td><td>II</td></tr>
<tr><td>Rumah Negara Golongan III Permanen</td><td>III</td></tr>
<tr><td>Rumah Negara Golongan IIA Permanen</td><td>II</td></tr>
</tbody></table>


thanks in advance..

.sst

 

[muhammad susanto]

hi Jack....thank you..
my roman number's target are ONLY I, II, III just it...]
possible problem
- text....IA.....
- text....I...
- text ...IIB...
- text....III...

 

[Rick Rothstein]

hi Jack....thank you..
my roman number's target are ONLY I, II, III just it...]

So the next Roman Numbers would be IIII, IIIII, IIIIII, etc.?

possible problem
- text....IA.....
- text....I...
- text ...IIB...
- text....III...

Although your example does not show it, I presume the letters are added to a given Roman Number in order (A, B, C, etc.), correct? If so, can we assume the letters will never go beyond the letter H?

 

[Joe4]

So the next Roman Numbers would be IIII, IIIII, IIIIII, etc.?

Rick,
The way that I am reading it, I, II, and III are the only options.

 

[muhammad susanto]

hi Rick...
in my previous post..i just wanna to pull/extract data only roman number ..
i think for make easy and simple, i try to make more simple my question...
cause my data contains only I,II,III or combination I,IIA,IIB,IIIA,IIIB...
oh...for letter only A,B,C with combination eg. IIA,IIB,IIIA,IIIC,IIC

 

[Rick Rothstein]

hi Rick...
in my previous post..i just wanna to pull/extract data only roman number ..
i think for make easy and simple, i try to make more simple my question...
cause my data contains only I,II,III or combination I,IIA,IIB,IIIA,IIIB...
oh...for letter only A,B,C with combination eg. IIA,IIB,IIIA,IIIC,IIC

With those restrictions, here is a UDF (user defined function) that you can use...

[table="width: 500"]
[tr]
	[td]Function GetRomanNumber(S As String) As String
  Dim X As Long, Arr() As String
  Arr = Split(S)
  For X = 0 To UBound(Arr)
    If Arr(X) = "I" Or Arr(X) = "II" Or Arr(X) = "III" Then
      GetRomanNumber = Arr(X)
      Exit Function
    ElseIf Arr(X) Like String(Len(Arr(X)) - 1, "I") & "[A-C]" Then
      GetRomanNumber = Left(Arr(X), Len(Arr(X)) - 1)
      Exit Function
    End If
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetRomanNumber just like it was a built-in Excel function. For example,

=GetRomanNumber(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[muhammad susanto]

hi Rick...thank you very much!!... GREAT!!!

 

[strooman]

Solution #10 wasn't working for you?

 

[muhammad susanto]

i miss it , this complete for overall roman number :

[COLOR=#333333]Function extractRoman(ByVal strInputText As String) As String[/COLOR]
Dim x As Long, y As Long
Dim result As String
Dim myMatch As Object, regEx As Object

    Set regEx = CreateObject("vbscript.regexp")
    strPattern = "(M{1,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})|M{0,4}(CM|C?D|D?C{1,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})|M{0,4}(CM|CD|D?C{0,3})(XC|X?L|L?X{1,3})(IX|IV|V?I{0,3})|M{0,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|I?V|V?I{1,3}))"
    
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With
    
    Set myMatch = regEx.Execute(strInputText)
    
    For x = 0 To myMatch.Count - 1
            result = result & myMatch.Item(x)
    Next

    extractRoman = result [COLOR=#333333]End Function[/COLOR]

hi strooman...BRILLIANT!!! working well.!!

 

[strooman]

Thanks for the feedback. Have to adjust it a little bit to tackle multiple instances of Roman numerals in one line

 

[Rick Rothstein]

i miss it , this complete for overall roman number :

[COLOR=#333333]Function extractRoman(ByVal strInputText As String) As String[/COLOR]
Dim x As Long, y As Long
Dim result As String
Dim myMatch As Object, regEx As Object

    Set regEx = CreateObject("vbscript.regexp")
    strPattern = "(M{1,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})|M{0,4}(CM|C?D|D?C{1,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})|M{0,4}(CM|CD|D?C{0,3})(XC|X?L|L?X{1,3})(IX|IV|V?I{0,3})|M{0,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|I?V|V?I{1,3}))"
    
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With
    
    Set myMatch = regEx.Execute(strInputText)
    
    For x = 0 To myMatch.Count - 1
            result = result & myMatch.Item(x)
    Next

    extractRoman = result [COLOR=#333333]End Function[/COLOR]

hi strooman...BRILLIANT!!! working well.!!

Does strooman's code really work correctly for you? Have you tried it with any text before the Roman Number you want that starts with an upper case I, V, X, L, C, D or M yet? I'm guessing your text before the Roman Number are names. I don't know names in your country, so I will use a famous American actor's name for example purposes. For this text...

Michael Douglas IIA Actor

You would want II returned to you; however, strooman's function returns MDII instead. Any name starting with an upper case letter that is a Roman Number "digit" letter will have that letter returned to you using strooman's code.