How to split a cell at the cursor?

[nikneven]

I have a survey with a significant amount of open ended questions. It order to code the survey I need to split each section of each open ended response into one cell. For example, the single cell: "Service was slow and I hated the food" would become two cells, "Service was slow" & "and I hated the food"

Each single cell with the original response are different lengths, and contain different amount of things that need to be split. So I was thinking that the easiest way to get that done was a short cut where, I click in the place that it needs to split, it selects everything to the right, cut, move one cell right, and then paste. I've been doing it manually as: "double click in cell at split point, ctrl+shift+end, ctrl x, tab, ctrl v" but that is really tedious and repetitive, which makes me think that there has to be a way to make a macro so the process would instead be, "double click in cell at split point, ctrl e"

Any help would be greatly appreciated!

 

[Jonmo1]

Unfortunately, once you double click into the cell, macros can no longer run.
It's called 'Edit Mode'
Once in edit mode, you can't run macros.

Event codes exist that can detect when you've double clicked a cell
But it can't then detect where in the cell your cursor is, or do anything about it even if it could.

There must be some common pattern about where the cell should be split.
In the middle?
After the nth space?
After or Before a specific key word (or list of specific keywords) ?

anything like that?

 

[nikneven]

Dang!

Unfortunately, there is absolutely no pattern. The questions was literally "Please share any comments you have regarding the service." So some people wrote 3 words, but some people wrote an essay.

Sigh. So I guess that means I need to do it all by hand.

 

[Jaafar Tribak]

I was intrigued by the problem arised in this thread and gave this a shot .. The following code successfully (at least for me) split the cell at the cursor point despite Excel being in Edit Mode

The code assumes that the cells to be split are located in Sheets(1)/Column A .. edit the code to suit your needs.

Workbook Demo

Place code in the Thisworkbook module:

'Purpose of code:
'===============
'Register a hot key (in this case F3) to split cells @ the cursor
'point despite excel being in Edit mode.
'Cells to be split are assumed to be in Sheets(1)-Column A..
'Change these to suit

Option Explicit
 
Private Type POINTAPI
    x As Long
    y As Long
End Type
 
Private Type Msg
    hWnd As Long
    Message As Long
    wParam As Long
    lParam As Long
    time As Long
    pt As POINTAPI
End Type

Private Declare Function RegisterHotKey Lib "user32" _
(ByVal hWnd As Long, ByVal id As Long, _
ByVal fsModifiers As Long, ByVal vk As Long) As Long

Private Declare Function UnregisterHotKey Lib "user32" _
(ByVal hWnd As Long, ByVal id As Long) As Long

Private Declare Function PeekMessage Lib "user32" _
Alias "PeekMessageA" _
(lpMsg As Msg, ByVal hWnd As Long, ByVal wMsgFilterMin As Long, _
ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long

Private Declare Function WaitMessage Lib "user32" () As Long

Private Declare Sub keybd_event Lib "user32.dll" _
(ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Private Const PM_REMOVE = &H1
Private Const KEYEVENTF_EXTENDEDKEY = &H1
Private Const KEYEVENTF_KEYUP = &H2
Private Const WM_HOTKEY = &H312
Private Const VK_F3 = &H72
Private Const VK_X = &H58
Private Const VK_CONTROL = &H11
Private bCancel As Boolean

Private Sub Workbook_Open()
    If ActiveSheet Is Sheets(1) And ActiveCell.Column = 1 Then Call HookHotKey
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call UnhookHotKey
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'cells to be split are in column A/Sheets1 .. change to suit
    If Sh Is Sheets(1) And Target.Column = 1 Then
        Call HookHotKey
    Else
        Call UnhookHotKey
    End If
End Sub

Private Sub HookHotKey()
    'hook the F3 key to activate the Macro.
    RegisterTheHotKey VK_F3
End Sub

Private Sub UnhookHotKey()
    'exit loop
    bCancel = True
    'restore ESC key default behaviour
    Application.EnableCancelKey = xlDisabled
    'restore F3 key default behaviour
    Call UnregisterHotKey(Application.hWnd, &HBFFF&)
End Sub

Private Sub RegisterTheHotKey(Key)
    Dim ret As Long

    bCancel = False
    'register the F3 hotkey
    ret = RegisterHotKey(Application.hWnd, &HBFFF&, 0, Key)
    'process the Hotkey messages
    ProcessMessages
End Sub

Private Sub ProcessMessages()
    Dim Message As Msg

    'prevent interruption of the this macro
    Application.EnableCancelKey = xlDisabled
    'loop until bCancel is set to True
    Do
       'wait for a message
        WaitMessage
        'check if it's a HOTKEY-message
        If PeekMessage _
        (Message, 0, WM_HOTKEY, WM_HOTKEY, PM_REMOVE) Then
            'if hotkey pressed then split the activecell with that to its right
            SendKeys "+^{END}", True
            keybd_event VK_CONTROL, 0, 0, 0
            keybd_event VK_X, 0, 0, 0
            keybd_event VK_X, 0, KEYEVENTF_KEYUP, 0
            keybd_event VK_CONTROL, 0, KEYEVENTF_KEYUP, 0
            DoEvents
            SendKeys "{TAB}", True
            ActiveSheet.Paste
            ActiveCell.EntireColumn.AutoFit
        End If
        'let the operating system process other events
        DoEvents
    Loop Until bCancel
End Sub

Note :
The above code must be edited in order to work in 64-bit platforms