Excel worksheet protected but can't use text to column function

maybe just use formulas to 'convert' the data into helper columns and then point all the formula to those helper columns.  If you can share examples (no private/confidential information) then we could help with some suggested formulas.

You are correct: even with the cells unlocked, "Text to Columns" is greyed out in a protected worksheet.

This is by design in Excel (and remains unchanged in Excel 365 as of 2026).

• "Text to Columns" modifies the worksheet's structure—it splits one column into multiple columns, shifts data, and inserts new cells.
• Excel's protection system is built to block all structural edits, regardless of whether individual cells are locked.
• Crucially: Checking "Allow formatting columns" under protection settings does not help—that only allows changing column width or number format, not splitting data.

VBA Macro (The Standard)

If your team uses "Text to Columns" daily, this is the only elegant solution. You create a button that does everything automatically—users never see the password, and they can never forget to re-protect the sheet.

Step-by-step setup:

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the code below (replace "YourPassword" with your actual password).

Sub RunTextToColumnsSafely()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' 1. Unprotect the sheet
    ws.Unprotect Password:="YourPassword"
    
    ' 2. Run Text to Columns on the currently selected range
    ' Change "Comma:=True" to Tab, Space, or Semicolon as needed
    Selection.TextToColumns Destination:=Selection.Cells(1, 1), _
        DataType:=xlDelimited, Comma:=True
      
    ' 3. Re-protect the sheet (re-enable PivotTables and Sorting)
    ws.Protect Password:="YourPassword", _
        AllowFiltering:=True, _
        AllowUsingPivotTables:=True
End Sub

 4. Close VBA. Go to the Developer tab > Insert > Button (Form Control), draw the button, and assign this macro to it.

 5. Done: Users just select their column, click the button, and the split happens instantly.

Power Query (Best for Recurring Data)

If your raw data gets refreshed weekly (e.g., a new CSV export), Power Query is superior because it automates the splitting every time you refresh, without ever touching the protected sheet.

How to do it:

1. Select your raw data range > Data tab > From Table/Range (check "My table has headers").
2. In the Power Query Editor, click the column you want to split > Split Column > By Delimiter (choose comma, space, etc.).
3. Click Close & Load to output the split data to a new worksheet.
4. Link your Pivot Tables to this new output sheet.
   Benefit: When new data arrives, right-click the query > Refresh—the split happens automatically, and your original protected sheet remains completely untouched.

Manual Unprotect/Reprotect (Simplest)

Use this for rare, one-off tasks where you personally handle the data.

1. Review tab > Unprotect Sheet (enter password).
2. Run Text to Columns from the Data tab.
3. Immediately go back to Review > Protect Sheet.
4. Pro Tip: Before clicking OK, ensure you check:

• Allow users to use PivotTables
• Allow users to Sort
• (This prevents users from getting stuck later).

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.