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

 

[JackDanIce]

Try:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"Rumah Negara Golongan ","")," Permanen",""))

 

[muhammad susanto]

hi Jack..

for "Rumah Negara Golongan IIA Permanen" the result is not correct...

 

[JackDanIce]

Try:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"Rumah Negara Golongan",""),"Permanen",""))

 

[muhammad susanto]

still not work, what different from before it?

 

[JackDanIce]

It works for me using the example data you posted. I removed the spaces after "Golongan" and before "Permanen" in the second suggestion

 

[muhammad susanto]

unfortunely, not working for me...

 

[Rick Rothstein]

It works for me using the example data you posted. I removed the spaces after "Golongan" and before "Permanen" in the second suggestion

The problem is the "A" your formula leaves for IIA... the OP only wants the Roman Number II from it.

 

[JackDanIce]

Good spot, my bad, thanks @Rick Rothstein.

To OP, your example suggests only characters "I" to be extracted or will you expect terms including "IX", "VXI" and they should remain as characters used in Roman numerals?

If so, can you list all characters to be kept please?

 

[strooman]

You can give this macro a try:

<strong>Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).</strong>

1. Copy the below code, by highlighting the code and pressing the keys <strong><span style="color:#FF0000;">CTRL</span></strong> + <strong><span style="color:#FF0000;">C</span></strong>
2. Open your workbook
3. Press the keys <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">F11</span></strong> to open the Visual Basic Editor
4. Press the keys <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">I</span></strong> to activate the Insert menu
5. Press <strong><span style="color:#FF0000;">M</span></strong> to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys <strong><span style="color:#FF0000;">CTRL</span></strong> + <strong><span style="color:#FF0000;">V</span></strong>
7. Press the keys <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">Q</span></strong> to exit the Editor, and return to Excel
8. In cell C2: =extractRoman(A2)

Function extractRoman(ByVal strInputText As String) As String

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
End Function

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Your_Line[/td][td="bgcolor:#0070C0"][/td][td="bgcolor:#0070C0"]Roman_Numerals[/td][/tr]
[tr][td]

2​

[/td][td]Rumah Negara Golongan I Permanen[/td][td][/td][td="bgcolor:#FFFF00"]I[/td][/tr]
[tr][td]

3​

[/td][td]Rumah Negara Golongan II Permanen[/td][td][/td][td="bgcolor:#FFFF00"]II[/td][/tr]
[tr][td]

4​

[/td][td]Of the letters used IVXLCDM commonly in Roman numerals[/td][td][/td][td="bgcolor:#FFFF00"]IVXLCDM[/td][/tr]
[tr][td]

5​

[/td][td]Rumah Negara Golongan IIA Permanen[/td][td][/td][td="bgcolor:#FFFF00"]II[/td][/tr]
[tr][td]

6​

[/td][td]Super Bowl XXX.[/td][td][/td][td="bgcolor:#FFFF00"]XXX[/td][/tr]
[tr][td]

7​

[/td][td]Years in Roman numerals: a.d. MCMLXXXIX = a.d. 1989.[/td][td][/td][td="bgcolor:#FFFF00"]MCMLXXXIX[/td][/tr]
[tr][td]

8​

[/td][td]This is the number 13, XIII[/td][td][/td][td="bgcolor:#FFFF00"]XIII[/td][/tr]
[tr][td]

9​

[/td][td]We live in the year 2018 wich is MMXVIII[/td][td][/td][td="bgcolor:#FFFF00"]MMXVIII[/td][/tr]
[tr][td]

10​

[/td][td]MCMXCIX[/td][td][/td][td="bgcolor:#FFFF00"]MCMXCIX[/td][/tr]
[tr][td]

11​

[/td][td]Combination of MMXVIII and CMA[/td][td][/td][td="bgcolor:#FFFF00"]CMMXVIIICM[/td][/tr]
[/table]

The downside, when your have multiple instances of Roman numerals. See row 11. I have to work on that.