Python Forum

Hi,

I am new to python programming and need your help.

I have a Notes Column in excel file which contains data as below

ROW 1: Country:USA

State:Virginia
Serial Number: 00 DB C0 B1 E3 D5 05 7B 57 BE 3A BB FF D1 62 D6 A7
Address: 23 xys lane

SSN:2345550404
Zip : 22102


ROW 2 : Country:India

State:Virginia
Serial Number: 01 DB C0 B1 E3 D5 05 7B 57 BE 3A BB FF D1 62 D6 A7
SSN:2345550404
ZIP:22033
City: Fairfax

I need to extract the Serial Number from the string and create a new column for it.

I have the below code to split based on (: & \n )but data is not consistent in each row so it is not splitting correctly.

 import openpyxl
import pandas as  pd

book = openpyxl.load_workbook(r'C:\Users\OneDrive - Fannie Mae\Info\test.xlsx')
user_data = book.get_sheet_by_name(str('test'))
df= pd.DataFrame([str(user_data[x][0].value) for x in range(1,user_data.max_row)])
df.columns=['PluginOutput']
df.columns = df.columns.str.replace(' ', '')
df1 = df.PluginOutput.str.split('\n',expand=True)

---

Please help !!!