Identify characters and split into different sheets

[lnjohnson]

How do i create a formula that scans a column, auto-identify similar characters(perhaps by first 3 characters) in a cell and then splits(extract) the cells with similar characters into different sheets?

[#2]

@lnjohnson 

You can use VBA code in Excel.

Here is an example of how you can achieve this:

1. Press "Alt + F11" to open the Visual Basic Editor.
2. Insert a new module by clicking on "Insert" and selecting "Module".
3. In the module, paste the following code:

Sub SplitCellsToSheets()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim cell As Range
    Dim firstThreeChars As String
    Dim lastRow As Long
    
    ' Set the source sheet
    Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your source sheet name
    
    ' Set the source range
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    Set sourceRange = sourceSheet.Range("A1:A" & lastRow) ' Assuming the data is in column A
    
    ' Loop through each cell in the source range
    For Each cell In sourceRange
        ' Get the first three characters of the cell value
        firstThreeChars = Left(cell.Value, 3)
        
        ' Check if a sheet with the first three characters exists, create one if it doesn't
        On Error Resume Next
        Set targetSheet = ThisWorkbook.Sheets(firstThreeChars)
        On Error GoTo 0
        If targetSheet Is Nothing Then
            Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            targetSheet.Name = firstThreeChars
        End If
        
        ' Copy the cell value to the target sheet
        targetSheet.Cells(targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1, "A").Value = cell.Value
    Next cell
End Sub

4. Update the code with the appropriate sheet names and column references as necessary.

5. Close the Visual Basic Editor.

6. Press "Alt + F8" to open the Macro dialog box.

7. Select the "SplitCellsToSheets" macro and click "Run".

This macro will scan the specified column (column A in this example) in the source sheet, identify cells with similar first three characters, and split them into different sheets based on those characters. Each sheet will be named after the first three characters, and the corresponding cells will be copied to their respective sheets.

Make sure to adjust the code to fit your specific needs, such as the column to scan and the sheet names. Also, remember to save your workbook as a macro-enabled (.xlsm) file format.

If you prefer to use formulas instead of VBA, you can achieve a similar result using formulas and filters. Here is an example:

Assuming you have your data in column A starting from cell A1, and you want to split the cells with similar first three characters into different sheets:

1. Create a new sheet for each group of similar characters that you want to split. Name each sheet based on the first three characters.
2. In the first sheet (e.g., Sheet1), enter the following formula in cell B1:

=LEFT(A1,3)

1. Drag the formula down to apply it to all the cells in column B, corresponding to your data range.
2. In the first sheet, go to the Data tab and click on the Filter button to enable filtering.
3. Click on the filter dropdown in column B and select one of the three-character values.
4. Copy the visible cells in column A (excluding the header) by selecting them and pressing "Ctrl + C".
5. Go to the corresponding sheet for that value (named after the three characters), select cell A1, and paste the copied values using "Ctrl + V".
6. Repeat steps 5 to 7 for each three-character value in the filter dropdown, copying and pasting the corresponding values to their respective sheets.

By applying filters and copying the visible cells for each value, you can split the cells with similar first three characters into different sheets manually using formulas and filtering in Excel.

[#3]

@NikolinoDE 

Thanks for the code. So far it partly works, but it pulls all the cells in the source range to a new sheet instead of splitting them into different sheets by firstThreeChars. For example:

ABC_123456
ABC_123457
ABC_123458
DEF18818
DEF18819
DEF18820
GHI207
GHI208
GHI209
GHI210
GHI211

Here's what I have entered for the code:

Sub SplitCellsToSheets()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourceRange As Range
Dim cell As Range
Dim firstThreeChars As String
Dim lastRow As Long

' Set the source sheet
Set sourceSheet = ThisWorkbook.Sheets("ABC") ' Replace "Sheet1" with your source sheet name

'Set the source range
lastRow = 19726
Set sourceRange = sourceSheet.Range("F2:F" & lastRow) ' Assuming the data is in column A

' Loop through each cell in the source range
For Each cell In sourceRange
' Get the first three characters of the cell value
firstThreeChars = Left(cell.Value, 3)

' Check if a sheet with the first three characters exists, create one if it doesn't
On Error Resume Next
Set targetSheet = ThisWorkbook.Sheets(firstThreeChars)
On Error GoTo 0
If targetSheet Is Nothing Then
Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
targetSheet.Name = firstThreeChars
End If

' Copy the cell value to the target sheet
targetSheet.Cells(targetSheet.Cells(targetSheet.Rows.Count, "F").End(xlUp).Row + 1, "F").Value = cell.Value
Next cell
End Sub

[#4]

@lnjohnson 

As a functional programming environment, Excel formulas cannot 'put' extracts anywhere except the cell in which the formula is written.  VBA, being an imperative programming language, can change the state of other cells.

So, a formula solution must have a formula that reads the 'sourceRange' on every sheet you wish to write to.

= LET(
    tail, TEXTAFTER(sourceRange, string),
    FILTER(tail, ISTEXT(tail))
  )

[The image shows the ranges on the same sheet for convenience]

[#5]

@lnjohnson 

It seems that the code provided is not correctly splitting the cells into different sheets based on the first three characters.

The updated code below should split the cells into different sheets based on the first three characters:

Sub SplitCellsToSheets()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim cell As Range
    Dim firstThreeChars As String
    Dim lastRow As Long
    
    ' Set the source sheet
    Set sourceSheet = ThisWorkbook.Sheets("ABC") ' Replace "Sheet1" with your source sheet name
    
    ' Set the source range
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "F").End(xlUp).Row
    Set sourceRange = sourceSheet.Range("F2:F" & lastRow) ' Assuming the data is in column F
    
    ' Loop through each cell in the source range
    For Each cell In sourceRange
        ' Get the first three characters of the cell value
        firstThreeChars = Left(cell.Value, 3)
        
        ' Check if a sheet with the first three characters exists, create one if it doesn't
        On Error Resume Next
        Set targetSheet = ThisWorkbook.Sheets(firstThreeChars)
        On Error GoTo 0
        If targetSheet Is Nothing Then
            Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            targetSheet.Name = firstThreeChars
        End If
        
        ' Copy the cell value to the target sheet
        targetSheet.Cells(targetSheet.Cells(targetSheet.Rows.Count, "F").End(xlUp).Row + 1, "F").Value = cell.Value
    Next cell
End Sub

Make sure to adjust the following parts of the code to match your specific workbook:

• Set sourceSheet = ThisWorkbook.Sheets("ABC"): Replace "ABC" with the name of your source sheet.
• Set sourceRange = sourceSheet.Range("F2:F" & lastRow): Adjust the range to match the column and row range of your source data.

After updating the code, run the SplitCellsToSheets macro, and it should split the cells into different sheets based on the first three characters. Each sheet will be named after the corresponding three characters, and the cells will be copied to their respective sheets.

[#6]

@NikolinoDE 

Maybe I'm missing something. I don't see any differences to the updated code that would split the data into different sheets. Everytime i run the macro it creates a new sheet with the entire range. Have you tried to run it with the example data i submitted, and if so, can you send me a screenshot?

[#7]

How to subtract two cells in Excel

[#8]

@lnjohnson 

is this in the right direction, does it fit? ...see file.

If not, please explain exactly (step by step, cell by cell, sheet by sheet) exactly what is to be done. If possible, include a file (without sensitive data) or photos. Additional information such as Excel version, operating system, storage medium would be an advantage. Thank you for your understanding.

[#9]

@NikolinoDE 

Thank you for responding. The idea is that the cell information starts the way you have listed in your document. Then the formula should divide the cell information into different sheets based on the first three characters of each cell. See attachment.

[#10]

The formula should work without me telling it look for a specific string of data; something like the first three characters should work fine. I have over 19,000 lines with over 500 different variables. I can't spend time with a formula that i have manually tell it to look for a specific string of data. I hope that makes sense.

[#11 · Accepted solution]

@NikolinoDE 

Success! I was able to use ChatGPT and it produced the following code. I had to ask it separately for the match code, then paste it into the original code, but it works perfectly now. Thanks:

[#12]

I'm glad you found a solution that helps you.
I wish you continued success with Excel!