I receive order information from technicians via Microsoft forms. The system that runs the department is based on outlook calendars, forms, and power automate. The system functions well, functions paperless, and added no extra cost to the company; as a 3rd party ERP would have.
The issue, for me, is that forms are not sophisticated leaving a lot to be desired. The order info is copy pasted to the order form body, submitted, and power automate populates on a calendar. The data is a long run on paragraph and a pain to clean, just trying to arrive at quantity and part number. Part numbers are generally 5 or more digits and I have not found a way to get excel to split out numbers at = to or > then 5 digit, frustrating. Example data below.
Parts:McMaster-Carr 1) 2 Packs of 100 each 91102A750 Zinc-Plated Steel Split Lock Washer for 1/4" Screw Size, 0.26" ID, 0.487" OD Ships today 3.02 6.04 2) 1 Pack of 100 each 90108A417 Zinc-Plated Steel USS Washer for 3/8" Screw Size, 0.438" ID, 1" OD Ships today 12.85 12.85 3) 1 Pack of 100 each 90108A032 Zinc-Plated Steel USS Washer for 7/16" Screw Size, 0.5" ID, 1.25" OD Ships today 16.58 16.58 4) 2 Packs of 50 each 90108A033 Zinc-Plated Steel USS Washer for 1/2" Screw Size, 0.562" ID, 1.375" OD Ships today 12.71 25.42
It would be very helpful if you posted your sample data in a workbook and attached it so that we can see your data and then mock up what you want your results to look like.
could just copy paste it into a cell, then start from there. That is how the data start, the whole mess in A1.
When I do what you want, it does not present as you have shown. Help us to help you and not make more work for me to help you.
@metalformerp56 What if you use Power Query to split the long text into rows, first by ")" and then by the first 6 spaces. It results in something like this:
However, the split by ")" must be performed twice. Can't explain why as the characters are the same when I check it in Excel. Perhaps someone with a more technical background can explain what going on.
Anyway, the attached file contains
the PQ solution.
@riny you could do split by ") " - i.e. closing parenthesis and a space - and at the same time Quote Character None (instead of "). This allowed me to split into 5 rows at one go.
= Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(") ", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
@riny That's awesome. I was able to get to a similar point, but several more sort by delimiters plus a transpose.
Nice work. :{)
