CODEWITHSUNDEEP
excelpython-3.xopenpyxl
mali30
mali30

Reputation: 57

How to remove certain characters from cell on Excel

I am new to python and I am trying to run a program that runs down a list of values on Excel, and removes the "^" character from every index. For example, if i have a column that has the following values (MS144573^, MS655072^, MS655282^, MS655177^, MS655073^) I would like each value to be replaced to (MS144573, MS655072, MS655282, MS655177, MS655073).

I am using the Openpyxl module.

I cannot identify whether cells in Excel are strings, are characters. Any ideas?

i have tried the following code:

import openpyxl
from openpyxl import workbook
path = "C:/Users/x/Desktop/x/x.xlsx"
wb = openpyxl.load_workbook(path)
sheet_read = wb.get_sheet_by_name("report")
sheet_write = wb.active


for i in range(1, 10):
 x = sheet_write.cell(row = i, column = 1).value
 y = len(x)
 if x[y] == '^':
    x = x[:y-1]

But i get this error:

^ y = len(x) TypeError: object of type 'NoneType' has no len()`

Upvotes: 0

Views: 3791

Answers (1)

Connor J
Connor J

Reputation: 570

You should use the re module.

import re

value = re.sub('^', '', input)   # where input is the values that you want substituted

This would replace all instances of ^ with nothing, effectively removing it, and storing it in value. If you have more than one to do you can iterate through the list and simply append them to a new list of cleaned values.

EDIT: If you need more info on how to do this leave a comment and ill update the code to reflect your question.

Upvotes: 1

Related Questions