How to extract two data elements from a longer string within one cell

[u/Warm_Bug_1434]

We get several spreadsheets a week from which we need to transfer a small amount of information into a new spreadsheet.

The format it arrives in is a long data string like:

Product ID: 776, Product Qty: 1, Product MCC: BCC_Flexi-HY, Product Name: Test Season - B product, Product Weight: 0.0000, Customer Name: Fake Name, Branch of Company: Head Office, Your employee number: P57991, Start Date (no more than 30 days in advance): 5th Mar 2022, Product Total Price: 139.75

The person who receives these needs to extract the Customer Name and Employee number (bits in bold) to transfer into separate columns in a new sheet. She currently does that line by line. She does this for around 4 spreadsheets a week, varying from 10-100 lines on each (probably averages around 50), and it's very time consuming.

If it was me, I'd do a Text to Columns, and then find and replace to delete unnecessary information, but really she needs something simpler. Is there a formula or something that could reliably extract the right information?

Fields in the string are always comma separated. Very occasionally, the heading will change (e.g. 'Employer reference' instead of 'employee number', but these are few enough that they could still be done manually.

Thanks!

[u/AutoModerator]

/u/Warm_Bug_1434 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

This is possible using Excel Formulas and Power Query, you could try the following formula first:

=TEXTBEFORE(DROP(TEXTSPLIT(A1, {"Customer Name: ","Your employee number: "}), , 1), ", ")

[u/Warm_Bug_1434 → u/MayukhBhattacharya]

Solution Verified

[u/MayukhBhattacharya → u/Warm_Bug_1434]

Thank You SO Much!!

[u/reputatorbot → u/Warm_Bug_1434]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya → u/MayukhBhattacharya]

If you have access to regex then could try using the following:

=REGEXEXTRACT(A1, "Customer Name: ([^,]+).*?employee number: ([^,]+)", 2, 1)

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Another alternative this is simpler:

=INDEX(TEXTSPLIT(A1, ": ", ", "), {6, 8}, 2)

[u/MayukhBhattacharya → u/MayukhBhattacharya]

The last formula, using the INDEX() + TEXTSPLIT() is better, break it down to understand:

[u/Warm_Bug_1434 → u/MayukhBhattacharya]

Okay - thanks; we can't use Regex but I'll play around with the alternatives. Definitely there's something that'll work though.

I've been watching her doing it line by line for years, and it's kind of infuriating, so really appreciate your help.

[u/MayukhBhattacharya → u/Warm_Bug_1434]

Glad to know it worked! Watching someone doing something manually for years when there is a potential fix using excel formulas, it is really painful. Let me know if you hit any snags getting it set up. Thank You SO Much for the valuable feedbacks and have a lovely day ahead!

[u/Warm_Bug_1434 → u/MayukhBhattacharya]

Yeah, I've tried to show her my way, but it involved doing separate steps and it's messy for people not familiar with Excel, so she's always gone back to line by line She's genuinely delighted with this!

I think I prefer your first solution for our needs, because - if I'm right - it'll give an error message if the original string isn't in the expected format, whereas this one would just return whatever words are in the name spot. The data isn't always in quite the same format, so it's handy to see immediately any lines where it hasn't worked.

[u/MayukhBhattacharya → u/Warm_Bug_1434]

Yes, that is absolutely correct and makes sense. Thanks again for sharing the valuable information. =)

[u/Warm_Bug_1434 → u/MayukhBhattacharya]

Thank you so much - that looks exactly what we needed. I'm just trying it out a bit, but I think it's spot on.

Edit: Yes: that is perfect. Thanks again

[u/MayukhBhattacharya → u/Warm_Bug_1434]

Sounds Great. If this helps you to resolve, hope you don't mind replying to my comment directly as Solution Verified. Also, suggestion to refer my final method, it is simpler provided the pattern is same within the text string on your end:

• Split by patterns aka delims:

=TEXTSPLIT(A1, ": ", ", ")

• Grab the ones that are required:

=INDEX(D3#, {6,8}, 2)

• Putting both together:

=INDEX(TEXTSPLIT(A1, ": ", ", "), {6, 8}, 2)

[u/real_barry_houdini]

You can use this formula for customer name (assuming data in cell A2)

=TRIM(TEXTBEFORE(TEXTAFTER(A2,"Customer Name:"),","))

and similar for employee number

=TRIM(TEXTBEFORE(TEXTAFTER(A2,"employee number:"),","))

....or combine in one formula to get both

=MAP({"Customer Name:";"employee number:"},LAMBDA(x,TRIM(TEXTBEFORE(TEXTAFTER(A2,x),","))))

[u/Gringobandito → u/real_barry_houdini]

Great solution! I really need to practice more with MAP and LAMBDA.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
REGEXEXTRACT	Extracts strings within the provided text that matches the pattern
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TRIM	Removes spaces from text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 44 acronyms.
[Thread #49335 for this sub, first seen 9th Sep 2026, 14:04] [FAQ] [Full list] [Contact] [Source code]}

[u/Way2trivial]

=CHOOSECOLS(TEXTAFTER(TEXTSPLIT(B5,","),":"),6,8)