how to sort Total exp column in an excel sheet which has in format XXYears XXMonths

Vijayvr If you have an older version of Excel (non-MS365), try the following formula for the first record in row 2, then copy it down:

=SUBSTITUTE(LOWER(LEFT(C2, SEARCH(" ", C2)-1)), "years", "")+SUBSTITUTE(LOWER(RIGHT(C2, LEN(C2)-SEARCH(" ", C2))), "months", "")/100

Note: this assumes all text strings follow the same pattern, where there are no spaces between the number and "years", or the number and "months" (the only space character present is to separate years from months). Also, if the Total Exp is 15 years and zero months, for example, it must read "15years 0months" (if it just says "15years", then the formula will return an error). The same goes for Total Exp of less than a year... it must read "0years 6months". And, of course, 1 year must be "years" (with an "s").

Then, you can use Filters to sort by the converted column:

Custom Text Conversion

Please see the attached workbook, if needed...

Vijayvr 

Try this formula in cell D2:

=TEXTJOIN(".",,TEXT(SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(LOWER(C2)," "),"years",""),"months",""),"0#"))

Then drag down to the bottom of your data. Note you will need to correct C3 from "Yeras" to "Years".

If you want to put it in a single formula without dragging down, you can just put this formula in cell D2:

=BYROW(C2:C9,LAMBDA(r,TEXTJOIN(".",,TEXT(SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(LOWER(r)," "),"years",""),"months",""),"0#"))))

However, if what you want to do is sort the spreadsheet, it would be best to create separate Year and Months columns and then sort on those. 

You can create both columns with this in C2:

=--SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(LOWER(C2)," "),"years",""),"months","")

If you want to use Power Query for this, you can create use this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Convert = Table.AddColumn(
        Source, 
        "Converted", 
        each Text.Combine(
            List.Transform(
                List.Select(
                    Splitter.SplitTextByAnyDelimiter({"years"," ","months","yeras"})(Text.Lower([Total Exp]))
                    , each _ <> ""
                ),
                each Text.PadStart(_, 2, "0")
            )
            ,".")
    )
in
    Convert