Looking to pull out certain numbers from a mix of text and symbols in Excel columns using Python?

Question

Looking to pull out certain numbers from a mix of text and symbols in Excel columns using Python?

I am new to automating tasks in Python involving Excel. I need assistance with extracting specific numbers that are surrounded by different characters within columns.

Actual DATA 

                Column A
     kDGK~202287653976 ~LD ~ 8904567
     SIP~12335678 ~202267858245~LD~8936272
     SIN112592~ LD ~ SIN112592
     0194X0322 ~ LD ~ 202243296291

Expected Output

                Column B
             202287653976
             202267858245
                  -
             202243296291

I want to extract 12 digits starting with "2022" and leave a blank cell for those that don't meet the condition. It seems like a simple task, but I can't seem to figure out how to do it.

Thank you in advance for your help.

python excel dataframe

Answer 1

Answer №1

If you want to extract a number in Python, you can use regular expressions:

import re
col_a = 'kDGK~202287653976 ~LD ~ 8904567'
match = re.search(r'(2022\d+)', col_a)
if match:
    col_b = match[0]

The variable match will be None if nothing is found, or it will be a "match object" - in this case, match[0] will give you the desired number.

UPDATE

The regex above will find "2022" followed by any number of digits. If you specifically want exactly 8 digits after "2022", you should use re.search(r'(2022\d{8})', col_a) instead.

UPDATE 2

If you are working with openpyxl, the complete code would look something like this:

from openpyxl import load_workbook
import re

wb = load_workbook('somefile.xlsx')
ws=wb.active

for row in range(len(ws['A'])): #number of used cells in column A
    match = re.search(r'(2022\d+)', ws.cell(row,1).value)
    if match:
        ws.cell(row,2).value = match[0]

Answer 2

If you want to extract a number in Python, you can use regular expressions:

import re
col_a = 'kDGK~202287653976 ~LD ~ 8904567'
match = re.search(r'(2022\d+)', col_a)
if match:
    col_b = match[0]

The variable match will be None if nothing is found, or it will be a "match object" - in this case, match[0] will give you the desired number.

UPDATE

The regex above will find "2022" followed by any number of digits. If you specifically want exactly 8 digits after "2022", you should use re.search(r'(2022\d{8})', col_a) instead.

UPDATE 2

If you are working with openpyxl, the complete code would look something like this:

from openpyxl import load_workbook
import re

wb = load_workbook('somefile.xlsx')
ws=wb.active

for row in range(len(ws['A'])): #number of used cells in column A
    match = re.search(r'(2022\d+)', ws.cell(row,1).value)
    if match:
        ws.cell(row,2).value = match[0]

Looking to pull out certain numbers from a mix of text and symbols in Excel columns using Python?

Answer №1

Similar questions

Transform the HTTP text response into a pandas dataframe

Encountering a 400 bad request error while trying to upload a file using a flask Ajax call

Using Python 3 to extract specific strings from an HTTP request's response

Advanced function - Python

Engaging with website components that modify HTML content values

Using Selenium to stream audio directly from the web browser

Every other time, Django's request.GET.get() method will return None

Modify the matplotlib demonstration to utilize a CSV file containing three-dimensional X, Y, and Z data

Unable to locate 'element by' during regular functioning

Tips for ensuring a page has fully loaded before extracting data using requests.get in Python without relying on an API

Python data type unit test results in failure due to unexpected values: Expected 'NaT' but instead received 'NaN'

Execute shutdown command through terminal upon detection of a rising edge signal from GPIO input

Saving User Inputs in Django

What is the best way to generate a list using user input in Python?

While going through multiple JSON links, I encountered a JSON decode error

Encountering Error: ImportError when working with Google App Engine locally: Module google.cloud.bigquery not found

Selenium mistakenly chooses the incorrect element by selecting the first sibling element instead of searching within the element itself

Performing cumulative sum operations on Pandas dataframes while satisfying specified conditions

When using Selenium in Python, the get_attribute() method retrieves the specific data of an image instead of the URL

Guide on clicking a label element with Python and Selenium