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

[Vijayvr]

Hi All

I have attached the excel sheet and i want to convert the column C Total Exp ( 15years 6months) into 15.06 in column D.

If the number of months is less than 10 it should show as 02,03,04...09

If the number of months is equal to 10 & 11 it should be 15.10 , 15.11

[#2]

@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

[#3]

@flexyourdata 

Hi Flex

I am unable to use the Formula 1 & Formula 2 . Attached sheet once again when i use Formula 1 i am getting error #NAME? and when i use Formula 2 i am getting error hence i have pasted the screenshot in Formula 2 tab.

I am new to power query so kindly help me how to start and execute the code which u have provided.

[#4]

@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...