Summarizing inconsistent text format in column

You could use a Python script for it: 

import pandas as pd
import numpy as np

def convert_to_minutes(row):
    # Original string
    time_string = row['Status 1']  

    # Predefined order of suffixes and their conversion factors to minutes
    time_units = {
        "Y": 365 * 24 * 60,  # Years to minutes
        "M": 30 * 24 * 60,   # Months to minutes (approximate, assuming 30 days per month)
        "w": 7 * 24 * 60,    # Weeks to minutes
        "d": 24 * 60,        # Days to minutes
        "h": 60,             # Hours to minutes
        "m": 1               # Minutes
    }

    # Create list for all the converted entries
    converted_times = []

    # Split by commas to handle multiple time components (e.g., "2w, 3d, 11h, 41m")
    for times in time_string.split(","):
        time_in_minutes = 0  # Initialize total time for this entry
        
        # Parse the string and calculate the total minutes for each part
        for part in times.split():
            suffix = part[-1]  # Get the suffix (last character)
            
            # Check if the suffix is valid and exists in the time_units dictionary
            if suffix in time_units:
                value = int(part[:-1])  # Extract the numeric value
                time_in_minutes += value * time_units[suffix]  # Convert and accumulate the total        
        
        # Append the calculated time for this part to the list
        converted_times.append(time_in_minutes)

    # Sum the total times for all parts in the row
    total_time = np.sum(np.asarray(converted_times))

    return total_time

# Apply the function to create a new column for total time
dataset['total time in minuts'] = dataset.apply(convert_to_minutes, axis=1).astype(int)

Resultat in: 

Did I solve your question? Mark my post as a solution! Kudos are appreciated as well as LinkedIn endorsements.