Function Problems

How to Extract Text Between Markers in an Excel QR Code String

John Wilson Sep 25, 2026 871 views

Question details

The user needs to extract a specific serial number located between two text markers, (S) and (P), from a scanned QR code string in a spreadsheet.

How to Extract Text Between Markers in an Excel QR Code String

Product: Excel
Device & OS: not provided
Scenario: Scanning QR codes into a spreadsheet and needing to parse specific data points from the complex string generated by the scan.
Observed behavior: The user is looking to isolate the exact text between two defined markers and needs a solution for both single scans and multiple scans grouped in one cell.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365, Excel 2024) or WPS Office, as the TEXTBEFORE and TEXTAFTER functions are newer text-handling arrays. If using an older version, alternative text functions will be required.

Solution 1Recommended

Use TEXTAFTER and TEXTBEFORE for Single Cell Extraction

This is the most efficient method to extract text between two specific markers from a single QR code string.

By nesting TEXTBEFORE inside TEXTAFTER, you instruct the spreadsheet to first trim everything after the second marker, and then trim everything before the first marker, leaving only the desired serial number.

Select the target cell

Click on the empty cell where you want the extracted serial number to appear (for example, B2 if your QR code string is in A2).

Enter the nested formula

Type the formula `=TEXTAFTER(TEXTBEFORE(A2,"(P)"),"(S)")` directly into the formula bar.

Apply and drag

Press the Enter key to calculate the result. Click the small square at the bottom-right corner of cell B2 and drag it down to apply the formula to the rest of your scanned list.

Use TEXTAFTER and TEXTBEFORE for Single Cell Extraction

Formula Customization: You can replace "(S)" and "(P)" in the formula with any other specific characters or strings that act as boundaries in your unique QR code format.

Solution 2

Extract Text from Multiple QR Codes in One Cell

Use this approach when you have scanned multiple QR codes (e.g., multiple RAM modules) into a single cell, separated by a delimiter like a comma or space.

Easily Extract Text and Manage Data with WPS Spreadsheet

WPS Office Spreadsheet provides comprehensive text extraction tools and advanced functions to handle complex data like QR code strings seamlessly and accurately.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your scanned QR code data.
2. Enter the extraction formula: Click on the blank cell next to your first QR code and type `=TEXTAFTER(TEXTBEFORE(A2,"(P)"),"(S)")`.
3. Autofill to complete the list: Press Enter, then double-click the fill handle in the bottom-right corner of the cell to instantly extract serial numbers for all rows.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsSupports modern text manipulation functions for advanced data parsingFree, lightweight, and user-friendly interface for daily data processing

microsoft office alternative - wps office

Frequently Asked Questions

What if my version of Excel doesn't support TEXTBEFORE and TEXTAFTER?

If you are using an older version, you can achieve the same result using a combination of MID, FIND, and LEN functions. The equivalent formula is `=MID(A2, FIND("(S)", A2) + 3, FIND("(P)", A2) - FIND("(S)", A2) - 3)`. Note that the '+ 3' and '- 3' account for the character length of your marker '(S)'.

How do I dynamically extract text without hardcoding the markers?

Instead of typing the markers directly into the formula, you can place your start marker in cell C1 and end marker in D1. Your formula would then become `=TEXTAFTER(TEXTBEFORE(A2, $D$1), $C$1)`, allowing you to change the markers anytime without rewriting the formula.

Can I use TEXTSPLIT and TEXTAFTER together for multiple scans?

Yes, if you have Microsoft 365 or a compatible spreadsheet software, you can wrap your formula in an array function. You can use TEXTSPLIT to break the string by its delimiter, and then apply MAP with LAMBDA to process the TEXTBEFORE/TEXTAFTER logic across the newly created array in a single formula step.

Excel & Data Problems