Run a macro from cursor position within a cell

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
Hello Forumers,

Is it possible to run a macro from the cursor position in a cell?

Example:

In cell A1 I have the following text:

The fox jumped over the fence.

My cursor position is at the letter t of 'the fence'.

I would like to select the words 'the fence', use a macro to copy these two words and paste them in cell C1, leaving the contents of A1 intact.

Harry
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Is this just a question of "can it be done"?
If you need this functionality, what conditions must the macro/formula abide by? (IE: Extract the last two words in a cell?)

Bear in mind that you don't need code for this as formulas can extract parts of text too.
 

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
Is this just a question of "can it be done"?
If you need this functionality, what conditions must the macro/formula abide by? (IE: Extract the last two words in a cell?)

Bear in mind that you don't need code for this as formulas can extract parts of text too.

Thank you for your response, RudiS,

What I would like to do is:

- Copy and paste any one word that I like from A1 with CTRL-a from the cursor position in A1 and paste the one word into C1

- Copy and paste any two words that I like from A2 with CTRL-b from the cursor position in A2 and paste the two words into C2

- Copy and paste any three words that I like from A3 with CTRL-c from the cursor position in A3 and paste the three words into C3

etc.

Regards,
Harry
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
Is it possible to run a macro from the cursor position in a cell?
No, it is not possible... VBA code cannot be run when the worksheet is in Edit Mode (which it would be if you have a text cursor situated inside the text in a cell).
 

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61

ADVERTISEMENT

Thank you, Mr. Rothstein, somehow I expected that it wouldn't be possible.
 

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
Would it be possible when the worksheet is *not* in Edit Mode to copy a number of consecutive words from a string of words in A1 and paste it in for instance C1, or D1?

For instance, what would be the codes to copy the following words from the following content in A1: one two three four five six seven eight nine ten:

two three four -> copy to C1
four five six seven -> copy to C1
six seven eight nine ten -> copy to D1

From the answers I hope to be able to adjust a macro in such a way that I can copy any consecutive number of words from A1 to any other cell.

PS: the trailing space (if any) after the last word of the copied words should be deleted when the words are placed in C1 (or D1)

Thank you for any assistance,
Harry
 
Last edited:

RudiS

Active Member
Joined
May 7, 2015
Messages
349

ADVERTISEMENT

You could use a formula to extract parts of the string, starting at an identified word and ending at another identified word

See the image here for the proposal...

=IFERROR(TRIM(MID(C4,SEARCH(A4,C4),IFERROR(FIND(" ",C4,SEARCH(B4,C4)),LEN(C4)+1)-SEARCH(A4,C4))),"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
Thanks again for your input, RudiS!
Sometimes it is easier to reference things by number as opposed to by name... here is another formula you can use that is based on the "field number" (the number of words in from the beginning of the text) to start at and the "word count" (the number of words to be returned)...

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",99)),FieldNumber*99,WordCount*99))

The red highlighted 99's need to be a number that is larger than the length of the text being parsed. You could use LEN(A1) in place of each of them, but I do not see a need to burden the formula with three additional function calls. Here is a copy of a layout similar to the one RudiS posted...

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;font-style: italic;;">Start Field Number</td><td style="font-weight: bold;font-style: italic;;">How Many?</td><td style="font-weight: bold;font-style: italic;;">String</td><td style="font-weight: bold;text-align: right;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;">Result</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style=";">one two three four five six seven eight nine ten</td><td style="text-align: right;;"></td><td style=";">four five six</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style=";">This is a string test</td><td style="text-align: right;;"></td><td style=";">a string</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style=";">Extract data from a string using formulas</td><td style="text-align: right;;"></td><td style=";">data from a string</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style=";">Apple Pear Grape Banana Strawberry Orange</td><td style="text-align: right;;"></td><td style=";">Grape Banana Strawberry Orange</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
And here is the formula that I used in cell E2 and copied down to E5...

=TRIM(MID(SUBSTITUTE(" "&C2," ",REPT(" ",99)),A2*99,B2*99))
 
Last edited:

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
Sometimes it is easier to reference things by number as opposed to by name... here is another formula you can use that is based on the "field number" (the number of words in from the beginning of the text) to start at and the "word count" (the number of words to be returned)...

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",99)),FieldNumber*99,WordCount*99))

The red highlighted 99's need to be a number that is larger than the length of the text being parsed. You could use LEN(A1) in place of each of them, but I do not see a need to burden the formula with three additional function calls. Here is a copy of a layout similar to the one RudiS posted...

Excel 2010
ABCDEF
1Start Field NumberHow Many?StringResult
243one two three four five six seven eight nine tenfour five six
332This is a string testa string
424Extract data from a string using formulasdata from a string
534Apple Pear Grape Banana Strawberry OrangeGrape Banana Strawberry Orange
6

<tbody>
</tbody>
Sheet1


And here is the formula that I used in cell E2 and copied down to E5...

=TRIM(MID(SUBSTITUTE(" "&C2," ",REPT(" ",99)),A2*99,B2*99))

Thank you, again, Mr Rothstein! I have a feeling this is going to work very fine. My knowledge of coding/formulas is insufficient yet to figure out where (in which cell) exactly I have to place the first formula =TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",99)),FieldNumber*99,WordCount*99)) ? (Which no doubt proves I am an absolute beginner ;)). As for the second formula =TRIM(MID(SUBSTITUTE(" "&C2," ",REPT(" ",99)),A2*99,B2*99))[/QUOTE] I assume the C2, A2 and B2 will be replaced by C3, A3 and B3 in row 3, by C4, A4 and B4 in row 4 and by C5, A5 and B5 in row 5, by selecting the formula and pulling it down to E5, am I correct?

Harry
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top