Ask Copilot to help you prepare Unique IDs for an in-list in PS Query (two ways).
About this article
This post will show you how to effectively prompt Microsoft Copilot (not to be confused with 365) to guide you with the problem described below.
What is Copilot and how can you use it?
Basic Terms of Blog
The Prompt (me or you): the text that the user types into the Copilot text field. Simply enter your question into the “Ask me anything…” box and select enter. For the most accurate responses, select the “Precise” conversation style.
The Executing Task (Copilot): this is the response that the AI tool will provide to your prompt(s).
The Problem
You and I are working with a list of IDs in Excel. We need to plug these IDs in a query so we can lookup some data. We know that we can use an "in-list" to do this, but need help preparing the data in the appropriate format. We would like to parse out these IDs in rows but limit to a maximum of 250 characters per row. We would also like to separate each ID with a comma and encase each ID with single quotes. How can we do this in Excel?
Now, let's start typing the following into Copilot:
- Begin prompt -
Me: I am working with a list of IDs in Excel (range A1:A100). I would like to parse out these IDS in rows but limit to a maximum of 250 characters per row. I would also like to separate each ID with a comma and encase each ID with single quotes. For example, I have the following data in column A:
ID
0336494
0433437
0590300
0606530
0617298
0641487
0658645
0667644
0688851
0688896
0699680
0707554
0720284
0720455
0720502
I need that data to look like this under column A:
0336494','0433437','0590300','0606530','0617298','0641487','0658645','0667644','0688851','0688896','0699680','0707554','0720284','0720455','0720502
It is important that the beginning and ending parts of the string do not include the single quote character.
- End prompt -
Executing Task: parse out IDs in Excel with a character limit per row, separate each ID with a comma, and encase each ID with single quotes.
To achieve this in Excel, you can use a combination of formulas and VBA (Visual Basic for Applications). Here’s a step-by-step guide:
Using VBA
1. Open the VBA Editor
- Press Alt + F11 to open the VBA editor.
- Insert a new module by clicking Insert > Module.
2. Paste the following VBA code:
Sub ParseIDs()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim result As String
Dim maxLength As Integer
Dim currentLength As Integer
Dim outputRow As Integer
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
Set rng = ws.Range("A2:A100") ' Adjust the range as needed
maxLength = 250
currentLength = 0
result = ""
outputRow = 1
For Each cell In rng
If cell.Value <> "" Then
Dim id As String
id = "'" & cell.Value & "',"
If currentLength + Len(id) > maxLength Then
ws.Cells(outputRow, 2).Value = Left(result, Len(result) - 1) ' Remove the last comma
outputRow = outputRow + 1
result = ""
currentLength = 0
End If
result = result & id
currentLength = currentLength + Len(id)
End If
Next cell
If result <> "" Then
ws.Cells(outputRow, 2).Value = Left(result, Len(result) - 1) ' Remove the last comma
End If
End Sub
3. Run the VBA Code
- Close the VBA editor.
- Press Alt + F8, select ParseIDs, and click Run.
This code will parse your IDs from column A and output the formatted strings in column B, ensuring each row does not exceed 250 characters.
Using Formulas (Alternative Method)
If you retain the current conversation with AI, you can ask it to modify its suggestions. Prompt windows are designed to stay active as long as the session is active or until the user closes it.
- Begin prompt -
Me: I prefer not to use VBA, but I can use Excel formulas. Can you provide a way that does not use VBA code please?
- End prompt -
Copilot: If you prefer not to use VBA, you can use Excel formulas, but it will be more complex and less flexible. Here’s a basic approach:
1. Add a Helper Column
In column B, use the formula to add single quotes around each ID:
= "'" & A2 & "',"
2. Concatenate IDs
In column C, use a formula to concatenate the IDs while checking the length. This will require a series of nested IF statements or a custom function, which can be cumbersome.
Using VBA is more efficient and flexible for this task. Let me know if you need any further assistance!
3. Supplementary prompt
Ask Copilot to edit the solution in order to:
a) Validate workability for an Excel version
b) Correct issue found with loss of leading zeroes
- Begin prompt -
Me: Will this solution work for Excel desktop 2019?
- End prompt -
Copilot: Yes, the VBA solution I provided will work for Excel desktop 2019. VBA (Visual Basic for Applications) is fully supported in Excel 2019, and the code provided does not use any features that are specific to newer versions.
- Begin prompt -
Me: I noticed that the leading zeroes in some of the values are being removed in the new column after I run the VBA script. Can you please fix this? Make sure to include the leading zeroes. You will probably need to first convert column A to a character type format. Make sure to always display 7 characters for each value in the list.
- End prompt -
Executing Task: parse out IDs in Excel with a character limit per row, separate each ID with a comma, and encase each ID with single quotes, ensuring leading zeroes are preserved and each ID has 7 characters
I understand the issue. Let’s update the VBA script to ensure that the leading zeroes are preserved and each ID is always displayed as a 7-character string. Here’s the revised VBA code:
- Open the VBA Editor:
- Press Alt + F11 to open the VBA editor.
- Insert a new module by clicking Insert > Module.
- Paste the following updated VBA code:
Sub ParseIDs()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim result As String
Dim maxLength As Integer
Dim currentLength As Integer
Dim outputRow As Integer
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
Set rng = ws.Range("A2:A100") ' Adjust the range as needed
' Convert the range to text format to preserve leading zeroes
rng.NumberFormat = "@"
maxLength = 250
currentLength = 0
result = ""
outputRow = 1
For Each cell In rng
If cell.Value <> "" Then
Dim id As String
id = "'" & Format(cell.Value, "0000000") & "',"
If currentLength + Len(id) > maxLength Then
ws.Cells(outputRow, 2).Value = Left(result, Len(result) - 1) ' Remove the last comma
outputRow = outputRow + 1
result = ""
currentLength = 0
End If
result = result & id
currentLength = currentLength + Len(id)
End If
Next cell
If result <> "" Then
ws.Cells(outputRow, 2).Value = Left(result, Len(result) - 1) ' Remove the last comma
End If
End Sub
- Run the VBA Code:
- Close the VBA editor.
- Press Alt + F8, select ParseIDs, and click Run.
This updated code ensures that each ID is formatted as a 7-character string with leading zeroes preserved. Let me know if this works for you or if you need any further adjustments!
That worked! Issue solved. We now have a solid prompt for the future. If you want to consolidate the conversation, then here is the full prompt for the most effective and efficient output:
You may want to consider adding one final prompt. Simply copy and paste the following text into your Copilot and check out the results!
- Begin prompt -
You: I am working with a list of IDs in Excel (range A1:A3000). I would like to parse out these IDS in rows but limit to a maximum of 250 characters per row. I would also like to separate each ID with a comma and encase each ID with single quotes. For example, I have the following data in column A:
ID
0336494
0433437
0590300
0606530
0617298
0641487
0658645
0667644
0688851
0688896
0699680
0707554
0720284
0720455
0720502
I need that data to look like this under column A:
0336494','0433437','0590300','0606530','0617298','0641487','0658645','0667644','0688851','0688896','0699680','0707554','0720284','0720455','0720502
It is important that the beginning and ending parts of the string do not include the single quote character. It also critical that leading zeroes are retained. You will probably need to first convert column A to a character type format. Make sure to always display 7 characters for each value in the list. Finally, I need a solution that will work in MS Excel desktop 2019.
- End prompt -
- There are different ways to achieve something.
- You will need to experiment with what works best for you.
- Be prepared to make several adjustments to your prompt and possibly your macro code.
- Remember to triple-check the output.
- The next time you plug in the same prompt you may get different output.
Learn more about prompt engineering!
Previous blog: Let's Ask Copilot! Limited Blog Series
#AI#copilot#cop