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

PMXGS.MKTCH.LOADLIBC	CMOCI0	B325
PMXRA.EXICH.LOADLIBC	CMOCI0	B325
PMXRA.PFRCH.LOADLIBC	CMOCI0	B325
PMXRA.ZBGCH.LOADLIBC	CMOCI0	B325
PMXRA.ZJJCH.LOADLIBC	CMOCI0	B325
PMXRS.CONCH.LOADLIBC	CMOCI0	B325

<tbody>
</tbody>

What I want it to look like is this:

A B C D
Dataset name Volume CUA Last two

PMXGS.MKTCH.LOADLIBC	CMOCI0	B325	CH
PMXRA.EXIBZ.LOADLIBC	CMOCI0	B325	BZ
PMXRA.PFR7C.LOADLIBC	CMOCI0	B325	7C
PMXRA.ZBGCH.LOADLIBC	CMOCI0	B325	CH
PMXRA.ZJJCM.LOADLIBC	CMOCI0	B325	CM
PMXRS.CONDH.LOADLIBC	CMOCI0	B325	DH

<tbody>
</tbody>

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

PMXGS.MKTCH.LOADLIBC	CMOCI0	B325
PMXRA.EXICH.LOADLIBC	CMOCI0	B325
PMXRA.PFRCH.LOADLIBC	CMOCI0	B325
PMXRA.ZBGCH.LOADLIBC	CMOCI0	B325
PMXRA.ZJJCH.LOADLIBC	CMOCI0	B325
PMXRS.CONCH.LOADLIBC	CMOCI0	B325

<tbody>
</tbody>

What I want it to look like is this:

A B C D
Dataset name Volume CUA Last two

PMXGS.MKTCH.LOADLIBC	CMOCI0	B325	CH
PMXRA.EXIBZ.LOADLIBC	CMOCI0	B325	BZ
PMXRA.PFR7C.LOADLIBC	CMOCI0	B325	7C
PMXRA.ZBGCH.LOADLIBC	CMOCI0	B325	CH
PMXRA.ZJJCM.LOADLIBC	CMOCI0	B325	CM
PMXRS.CONDH.LOADLIBC	CMOCI0	B325	DH

<tbody>
</tbody>

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.