Excel: Splitting a column with different types of data

microsoft excel

an Excel split column questions for you all.

Let's say I need to split all cells in column A.

Each cell contains multiple bits of spatial data, but doesn't always contain the same value ranges. Example:

A1: FS13, 1000N, 10-20CM
B1: 1000N, 20-30
C1: FS18, 1017N, LVL2, 20-30CM

I would need new columns for FS, N, LVL, and CM. But as you'll notice, not each cell contains all spatial data, and some cells have that information out of order relative to other cells in the column. Is there a way to split AS WELL AS sort the data into the proper order for the new columns?

Best Answer

Create a header row that shows the four possibilities in the order desired. The use CONCAT in an array formula:

=CONCAT(IF(ISNUMBER(SEARCH(B$1,TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),(ROW($1:$4)-1)*999+1,999)))),TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),(ROW($1:$4)-1)*999+1,999)),""))

Being an array formula one must confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),(ROW($1:$4)-1)*999+1,999)) is the part that iterates through the string breaking it into its parts based on the ,.

The we Search for the title in each of those parts and if it is there we return it to the CONCAT or a null string.

Once the new formula are released from microsoft we can replace the (ROW($1:$4)-1) parts with SEQUENCE(4,,0) and the need to array enter is no longer needed.

To install the macro

Open Excel -> Alt+F11 -> Insert -> Module -> paste the following code -> Ctrl+S -> and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the drop-down list

Sub ToManyColumns()
    Dim firstCellRow As Long
    firstCellRow = 1              'change this if you don't want to start at A1
    Dim firstCellColumn As Long
    firstCellColumn = 1           'change this if you don't want to start at A1
    
    Application.ScreenUpdating = False
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    Dim column As Long
    column = firstCellColumn
    Dim startIndex As Long
    Dim endIndex As Long
    Dim lastRow As Long
    lastRow = firstCellRow
    
    Do While True
        'find the range to copy
        startIndex = ActiveCell.row
        Do While ActiveCell.Value <> ""
            endIndex = ActiveCell.row
            ActiveCell.Offset(1).Activate
        Loop
        
        lastRow = ActiveCell.row
        
        Range(Cells(startIndex, firstCellColumn), Cells(endIndex, firstCellColumn)).Select
        Selection.Copy
        Cells(firstCellRow, column).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
        'get back to last rowIndex
        Cells(lastRow, firstCellColumn).Activate
        ActiveCell.Offset(1).Activate
        
        If ActiveCell.Value = "" Then Exit Do
        
        column = column + 1
    Loop
    
    'cleanUp -------------------------------------------
    Dim deleteFrom As Long
    Dim deleteTo As Long
    deleteTo = ActiveCell.row
    
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    Do While ActiveCell.Value <> ""
        ActiveCell.Offset(1).Activate
    Loop
    deleteFrom = ActiveCell.row
    
    Range(Cells(deleteFrom, firstCellColumn), Cells(deleteTo, firstCellColumn)).Select
    Selection.ClearContents
    
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    'cleanUp -------------------------------------------
    
    Application.ScreenUpdating = True
End Sub

To run the macro:

Make sure that you're in the worksheet you want to be! And click anywhere into the worksheet! (Because this macro runs in the Activated Worksheet)
Alt+F11 -> Click anywhere into the code -> press F5

You can also create a button in the worksheet and assign the macro to it - it's more user-friendly and you don't have to check that you're in the right worksheet.

Notes

If you don't want the macro to start at cell A1 (for example: start at different column), then change the numbers on 3rd and 5th lines.

For this purpose it's better to preprocess the data than use macros...

Excel – Copy all cells numeric data of one column to single cell

If you are using Office 365 Excel then you can use this array formula:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER($Q$2:INDEX(Q:Q,MAX(IFERROR(MATCH("zzz",Q:Q),1),IFERROR(MATCH(1E+99,Q:Q),1)))),$Q$2:INDEX(Q:Q,MAX(IFERROR(MATCH("zzz",Q:Q),1),IFERROR(MATCH(1E+99,Q:Q),1))),""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Which will concatenate any in column Q that are numbers.

Since array formulas should limit the data references, the:

$Q$2:INDEX(Q:Q,MAX(IFERROR(MATCH("zzz",Q:Q),1),IFERROR(MATCH(1E+99,Q:Q),1)))

automatically finds the last cell in column Q that has either a number or text in it. This makes it so the iterations are limited to only the data and allow for growth or shrinkage of the data set without the need to reset the formula references.

Best Answer

Related Solutions

Excel – Split long column into multiple columns by empty cell

To install the macro

To run the macro:

Notes

Excel – Copy all cells numeric data of one column to single cell

Related Question