How to save split text into excel file?

Question

I have an excel file containing a column of text with special characters such as the following:

Hi, My name is : Sam : and I live in : New York:
Hi, My name is : Samantha : and I live in : New Jersey:
Hi, My name is : John : and I live in : England:
Hi, My name is : Samuel : and I live in : India:

and i want to separate each rows of text by the separator colon separator ":" and this would give me two text one "Sam" and the other "New York". How do i save these two items in a separate excel file after separating each lines of rows?

First, i have tried to save the column into a list and use the for loop to go through each rows to separate the text on each rows. These has worked to separate each rows by the delimiter ":" however i am having problem saving the separated text

l = 0
for i in range(0,2):
    print(infolist[l].split(sep=':'))
    l+=1

Answer 1

Assuming you have the initial code block saved as a .csv file you can do this pretty easily:

import csv

with open('test.csv', 'r') as file:
    my_reader = csv.reader(file, delimiter=':')
    for row in my_reader:
        print(row)

Where you specify the ":" as a delimiter

Output:

['Hi, My name is ', ' Sam ', ' and I live in ', ' New York', '']
['Hi, My name is ', ' Samantha ', ' and I live in ', ' New Jersey', '']
['Hi, My name is ', ' John ', ' and I live in ', ' England', '']
['Hi, My name is ', ' Samuel ', ' and I live in ', ' India', '']

I believe it would work with other file extensions as well and you just need to change 'test.csv' to your file name. Additionally csv comes standard with python so there is no need to pip install anything!

If this isn't what you were asking I think you need to rephrase your question and be more specific