Run a macro from cursor position within a cell

[HarrySiers]

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

 

[RudiS]

Amazing formula Rick! I'm blown away!!!
Its really incredible what a few combinations of functions and a bit of creativity can do!

Cheers

 

[RudiS]

...My knowledge of coding/formulas is insufficient yet to figure out where (in which cell) exactly I have to place the first formula...

There is only one formula Harry. The first one that Rick typed was just an explanation of it (the syntax).

Column A and B are just the numbers to determine the words to extract
Column C is the text string to refer to
Column E is where the formula will go.

Here is a cross section of the actual worksheet:

Excel 2012
	A	B	C	D	E
1	Start Field Number	How Many?	String		Result
2	4	3	one two three four five six seven eight nine ten		four five six
3	3	2	This is a string test		a string
4	4	8	Extract data from a string using formulas		a string using formulas
5	3	4	Apple Pear Grape Banana Strawberry Orange		Grape Banana Strawberry Orange
Sheet2

Cell Formulas
Range	Formula
E2	=TRIM(MID(SUBSTITUTE(" "&C2," ",REPT(" ",99)),A2*99,B2*99))

 

[Rick Rothstein]

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))

The first formula and the second formula are both the same... the first formula is a generic one, you have to change the appropriate parts... A1 is just a cell reference, change it to the address of the cell containing your first text string (what C2 was in my second formula)... FieldNumber needs to be replaced by a number, or a reference to a cell containing a number (what A2 was in my second formula), that represents the count of the word you want to start with where the first word would be 1, the second word would be 2, and so on... WordCount needs to be replaced by a number or a reference to a cell containing a number (what B2 was in my second formula) that represents how many words you want the formula to display.

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?

Yes, you would put the formula you construct by replacing the appropriate parts mentioned above into a cell and then copy it down as you described... because the cell references do not have any $ signs (how you make part of a cell reference "absolute") in them, the cell references will change as the formula is copied down.

 

[HarrySiers]

The first formula and the second formula are both the same... the first formula is a generic one, you have to change the appropriate parts... A1 is just a cell reference, change it to the address of the cell containing your first text string (what C2 was in my second formula)... FieldNumber needs to be replaced by a number, or a reference to a cell containing a number (what A2 was in my second formula), that represents the count of the word you want to start with where the first word would be 1, the second word would be 2, and so on... WordCount needs to be replaced by a number or a reference to a cell containing a number (what B2 was in my second formula) that represents how many words you want the formula to display.



Yes, you would put the formula you construct by replacing the appropriate parts mentioned above into a cell and then copy it down as you described... because the cell references do not have any $ signs (how you make part of a cell reference "absolute") in them, the cell references will change as the formula is copied down.

Fascinating and although I am a beginner I see the beauty of using numbers instead of names here. Thanks, a lovely world to step into .

 

[HarrySiers]

Fascinating and although I am a beginner I see the beauty of using numbers instead of names here. Thanks, a lovely world to step into .

It works very well, Mr. Rothstein (you knew that of course , thanks again, also your explanation is very clear, instructive and inspiring!

 

[HarrySiers]

The first formula and the second formula are both the same... the first formula is a generic one, you have to change the appropriate parts... A1 is just a cell reference, change it to the address of the cell containing your first text string (what C2 was in my second formula)... FieldNumber needs to be replaced by a number, or a reference to a cell containing a number (what A2 was in my second formula), that represents the count of the word you want to start with where the first word would be 1, the second word would be 2, and so on... WordCount needs to be replaced by a number or a reference to a cell containing a number (what B2 was in my second formula) that represents how many words you want the formula to display.

Yes, you would put the formula you construct by replacing the appropriate parts mentioned above into a cell and then copy it down as you described... because the cell references do not have any $ signs (how you make part of a cell reference "absolute") in them, the cell references will change as the formula is copied down.

Hello, Mr. Rothstein, trying to learn a bit more about Excel, I was wondering how the formula would have to be adjusted (if possible at all) if one would want WordCount *not* to be replaced by a number or a reference to a cell containing a number (what B2 was in your second formula) that represents how many words one wants the formula to display, but by a number or a reference to a cell containing a number that would represent "End Field Number" instead of "How Many". For instance, if B1 in the diagram below would be "End Field Number", and B2 would be filled in as 6 (the sixth word in the "String" in C2, instead of the "3" words starting at the position of the "Start Field Number" in A2, what would the formula be to produce the same result in E2? Is it possible at all?

	A	B	C	D	E
1	Start Field Number	How Many?	String		Result
2	4	3	one two three four five six seven eight nine ten		four five six
3	3	2	This is a string test		a string
4	4	8	Extract data from a string using formulas		a string using formulas
5	3	4	Apple Pear Grape Banana Strawberry Orange		Grape Banana Strawberry Orange

<colgroup><col style="background-color: #DAE7F5"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2
​

Worksheet Formulas Cell Formula E2 =TRIM(MID(SUBSTITUTE(" "&C2," ",REPT(" ",99)),A2*99,B2*99)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>