Extract last two characters from the middle of a dataset name

[markzasz]

I have multiple files containing thousands of datasets names and I need to extract the last two characters from the second level of the dataset, below is an example of a files and what I am looking for.

file before:

A B C
Dataset name Volume CUA
[TABLE="width: 0"]
<tbody>[TR]
[TD]PMXGS.MKTCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[/TR]
[TR]
[TD]PMXRA.EXICH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[/TR]
[TR]
[TD]PMXRA.PFRCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[/TR]
[TR]
[TD]PMXRA.ZBGCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[/TR]
[TR]
[TD]PMXRA.ZJJCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[/TR]
[TR]
[TD]PMXRS.CONCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[/TR]
</tbody>[/TABLE]


What I want it to look like is this:

A B C D
Dataset name Volume CUA Last two
[TABLE="width: 0"]
<tbody>[TR]
[TD]PMXGS.MKTCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[TD] CH
[/TD]
[/TR]
[TR]
[TD]PMXRA.EXIBZ.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[TD] BZ
[/TD]
[/TR]
[TR]
[TD]PMXRA.PFR7C.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[TD] 7C
[/TD]
[/TR]
[TR]
[TD]PMXRA.ZBGCH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[TD] CH
[/TD]
[/TR]
[TR]
[TD]PMXRA.ZJJCM.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[TD] CM
[/TD]
[/TR]
[TR]
[TD]PMXRS.CONDH.LOADLIBC
[/TD]
[TD] CMOCI0
[/TD]
[TD] B325
[/TD]
[TD] DH
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Mark

 

[JLGWhiz]

some VBA

Sub t()
Dim c As Range, spl As Variant
With ActiveSheet
    For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        spl = Split(c.Value, ".")
        c.Offset(, 3) = Right(spl(1), 2)
    Next
End With
End Sub

 

[Rick Rothstein]

Assuming your examples are representative of your actual data, then here is a formula that you can use...

=RIGHT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",200)),200,200)),2)

 

[akschary]

I have multiple files containing thousands of datasets names and I need to extract the last two characters from the second level of the dataset, below is an example of a files and what I am looking for.

file before:

A B C
Dataset name Volume CUA
[TABLE="width: 0"]
<tbody>[TR]
[TD]PMXGS.MKTCH.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[/TR]
[TR]
[TD]PMXRA.EXICH.LOADLIBC[/TD]
[TD] CMOCI0 [/TD]
[TD] B325[/TD]
[/TR]
[TR]
[TD]PMXRA.PFRCH.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[/TR]
[TR]
[TD]PMXRA.ZBGCH.LOADLIBC [/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[/TR]
[TR]
[TD]PMXRA.ZJJCH.LOADLIBC[/TD]
[TD] CMOCI0 [/TD]
[TD] B325[/TD]
[/TR]
[TR]
[TD]PMXRS.CONCH.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[/TR]
</tbody>[/TABLE]


What I want it to look like is this:

A B C D
Dataset name Volume CUA Last two
[TABLE="width: 0"]
<tbody>[TR]
[TD]PMXGS.MKTCH.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[TD] CH[/TD]
[/TR]
[TR]
[TD]PMXRA.EXIBZ.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[TD] BZ[/TD]
[/TR]
[TR]
[TD]PMXRA.PFR7C.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[TD] 7C[/TD]
[/TR]
[TR]
[TD]PMXRA.ZBGCH.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[TD] CH[/TD]
[/TR]
[TR]
[TD]PMXRA.ZJJCM.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[TD] CM[/TD]
[/TR]
[TR]
[TD]PMXRS.CONDH.LOADLIBC[/TD]
[TD] CMOCI0[/TD]
[TD] B325[/TD]
[TD] DH[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
There is a simple forumala for this =MID(A1,10,2)

 

[Rick Rothstein]

There is a simple forumala for this =MID(A1,10,2)

Looks like you made a good observation. If, in fact, each of the first two fields are always five characters long, then your simple formula will work fine. However, if the fields can vary in length, then the OP would need the formula I posted in Message #3 instead.