import pandas as pd

# Load the uploaded Excel file
file_path = '/mnt/data/dummy_sales_data_modified.xlsx'
df = pd.read_excel(file_path)

# Check the first few rows of the dataframe to understand its structure
df.head()

                     Date  Sales Weather Last2Letters
0 2023-11-19 16:36:51.746    714  Normal           al
1 2023-11-20 16:36:51.746    792  Normal           al
2 2023-11-21 16:36:51.746    726   Sunny           ny
3 2023-11-22 16:36:51.746    735  Normal           al
4 2023-11-23 16:36:51.746    530  Normal           al

import xlsxwriter

# Create a new Excel file to write the modified data
modified_file_path = '/mnt/data/dummy_sales_data_with_regex.xlsx'

# Write the existing data and add the new column with REGEXEXTRACT formula
with pd.ExcelWriter(modified_file_path, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='Sheet1')

    # Access the workbook and the worksheet
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']

    # Define the REGEXEXTRACT formula to extract last two characters
    # As xlsxwriter does not directly support Google Sheets formulas,
    # the formula is written as a string
    for row_num in range(1, len(df) + 1):  # Adding 1 as Excel rows are 1-indexed
        regex_formula = f'=REGEXEXTRACT(C{row_num+1}, ".{{2}}$")'
        worksheet.write_formula(row_num, 4, regex_formula)

# Provide the path for download
modified_file_path

'/mnt/data/dummy_sales_data_with_regex.xlsx'

Excel Data: Extract Last2Chars

Google sheets assistant designed to automate complex Excel tasks. It intelligently manipulates spreadsheets, inserting formulas and charts as needed, simulating the work typically done by skilled Excel users