Macro for CTRL+RIGHT then Backspace

[ChingChoke]

I have a need to frequently edit text in a cell. The text is always in the sequence "string1 spaces string2" where string can be any length and any type of character. I want to delete the leading string1 and spaces, ending up with just string2 (which may contain many spaces). Any length of spaces separate the strings. After F2, the keystrokes "Home, Ctrl+Right and then Backspace" do this perfectly. Examples:

123 abc
1234 abcde
AbCdEf 123AbcDE

I cannot figure out how to generate a simple macro that will do this (and cannot find past postings for same).

Any advice greatly appreciated.

 

[eddvrs]

hey.

Give this a go:

        tx = Split(ActiveCell.Value, " ")
        new_text = tx(UBound(tx))

You could use that in a macro like this. I've added comments so you can better understand what each part does.

Sub second_part_after_spaces()

    Range("A1").Activate ' Or which ever cell is at the top of your column
    Do Until ActiveCell.Value = ""
        ' Creates an array called tx. Array takes activecell.value and seperates where there are spaces
        tx = Split(ActiveCell.Value, " ")
        
        ' UBound(tx) finds the last element in the tx array.
        new_text = tx(UBound(tx))

        ActiveCell.Offset(1).Activate
    Loop

End Sub

 

[Jonmo1]

Try the built in Find/Replace tool.

Find What : *space
Replace With : Nothing

 

[kennedy_clan]

try either
Activecell = mid (activecell, instr (1, activecell, " ")+ 1, 99)

Or
sendkeys "{F2}+^{RIGHT} {DEL}{ENTER}

 

[ChingChoke]

Thank you eddvrs for your advice. I couldn't get your code to work for my problem - kept generating a blank cell. But, it did get me digging into string manipulation (I wasn't familiar with Split) so thank you for that. (See my other comments below for what worked.)

Jonmo1, your solution works to remove String1 plus 1 space (or other amount predefined), but not when there is an unknown number of spaces.

kennedy_clan, your approach is closest I think. I'm tweaking it for my data and seems to be getting me results.

Here's what I ended up using and it's working but it's clumsy (and relies on 2 or more characters at the start of String1, so not ideal):

ActiveCell.Select
A = Trim(Right(ActiveCell.Value, Len(ActiveCell.Value) - 2))
ActiveCell.Value = A

Note I am using two lines of code to reassign "A" value to ActiveCell. Not sure that's necessary but couldn't get it to work otherwise.

Thanks everyone for your input - very helpful to getting my brain engaged.

CC