Future of Midlands Work
Technical Challenge
This task began with an excel table containing only the data in the Location column (B). I needed an excel formula which would both “trim” as well as “concatenate” the strings, all within this column and place the output strings into the Asset No. column (C). Sample data in the above table is representative.
Productivity goal
The choice was to either spend a couple of boring hours joining the first ID with the second ID in the B Location column and manually typing them into the C Asset No. column. This method would clearly have also been prone to human error.
Or I could spend 20 – 30 mins trialling prompts into, firstly, Chat GPT to see if it could come up with a formula to complete this task automatically. Crucially, I was not 100% sure that this would have even been possible prior to this experiment.
Chat GPT Attempts
In CGPT, my first couple of prompts and results were encouraging, but there were errors making them not usable. Which was when I ran out of free prompts. 🤦🏻♂️
I therefore now needed to run this in an alternative AI. And the clock was ticking.
Perplexity Solution
My previous perception of Perplexity.ai had been that it is a high quality, albeit lesser known AI than CGPT. And true enough, it’s output after my first prompt showed real promise.
The second prompt was a correction of the first and on this occasion, the output was thankfully 100% correct. It also auto-filled each C column on each of the sheets in the workbook once I input the formula into sell C2 on each. It all took about 10 minutes in Perplexity.
See below for method and prompts.
Conclusion
The biggest takeaway, from now on is just to give it a go. These labour-saving options can work out succesfully if we –
1. Have some awareness from the outset that it is actually possible for the AI to produce a solution.
2. Heed the advice about how to prompt the AI and to work through to successful outputs
3. Tweak the prompts until they start perfecting the ouputs
4. Once resolved, to take note of your method and your successful prompts and outputs
5. Share your success with other collaborators with a view to reciprocal tips in return
Method and prompt
In both Chat GPT as well as Perplexity, I uploaded a mocked-up spreadsheet of the sample data.
Prompt 1;
I am seeking a tool or a formula to trim, amalgamate and clean the detail in column B and enter the altered version it into column C as Door no. For example, the detail in cell Workshop 501-6-78 to 501-6-87 becomes 501-6-78/87
New (2nd) Prompt
I have input that formula, however the order of the output is not quite right. Eg. the output in cell C2 has come out as 501-6-78 /501-6-87 instead of 501-6-78/87. Is there a refined formula for this?
New Answer
A refined formula can extract only the base code from the first door number and then pull out just the last section (after the last hyphen) of the second door number after “to”, combining them as “501-6-78/87”. This approach works well for the format in your sheet.
Improved Excel Formula
You can use this updated formula in column C:
text
=IFERROR(
LEFT(
MID(B2,SEARCH(“501”,B2),SEARCH(“to “,B2)-SEARCH(“501”,B2)),
LEN(
MID(B2,SEARCH(“501”,B2),SEARCH(“to “,B2)-SEARCH(“501”,B2))
)-1
)
&
“/”
&
TRIM(RIGHT(SUBSTITUTE(MID(B2,SEARCH(“to “,B2)+3,99),”-“,REPT(” “,99)),99)),
“”
)
• This extracts “501-6-78” and then only “87” from “501-6-87”.Door-ID-Automation-Excercise.xlsx
• The RIGHT(SUBSTITUTE(…)) trick finds the last section after the hyphen.
Usage
• Paste this formula in cell C2 and drag down for other rows.
• This will yield results like “501-6-78/87” and “501-6-87/01”.Asset-ID-Automation-Excercise.xlsx
This solution presents your Asset numbers in the desired format and handles typical layouts in your data.Asset-ID-Automation-Excercise.xlsx