Returning portion of cell text

NMatt

New Member
Joined
Aug 24, 2007
Messages
17
I'm looking to copy text from a certain cell, probably assign it to a variable, and then paste it in another. The complication is that I only want a portion of text from the source cell. Specifically there will always be a chunk of text somewhere in the middle of this source cell; I want to grab all text to the right of this specific chunk of text. I suspect the MID function is involved, but I'm way too much of rookie to figure this out, because the number of characters before the specific chunk of text in the source cell will vary, and the number of characters to the right of the specific chunk (the ones I want to copy elsewhere) will also vary.

As an example, let's say the source cell is A1, and the text in it is:

BlueCHUNKElephant

CHUNK is the text that will always appear somewhere in the cell. The text I want to copy elsewhere is Elephant.

But in another case, the text in A1 is:

PurpleCHUNKAlbatross

In this case, Albatross is the text I want to copy elsewhere.
Any ideas? Thanks in advance!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
This can be done without VBA:

=REPLACE(A1,1,FIND("CHUNK",A1)+4,"")
 
Upvote 0
Thanks!
In your example, is the +5 based on the number of letters in CHUNK?
Also, once the MID function grabs the text, how would I then access it or assign it to a variable?
 
Upvote 0
If you still want it in VBA, that's a little different, and in this case 5 is due to the length of the word CHUNK:

Code:
Sub test()
Dim a As String, b As String, t As Long
a = Range("A1")
t = InStr(a, "CHUNK")
b = Mid(a, t + 5)
MsgBox b
End Sub
 
Upvote 0
Ah, I've confused you by being ambiguous.

This wouldn't be done interactively, so I'm not looking for a message box. It's going to run as a macro.
After I've retrieved the text as described above, I would want to execute some code to place the retrieved text into a merged cell defined as:
Range("B31:B40")

And actually the source cell is not A1, it's also a merged cell Range("A3:R3")

So would the code (up to certain point) be something like:
Code:
Sub test()
Dim a As String, b As String, t As Long
a = Range("A3:R3")
t = InStr(a, "CHUNK")
b = Mid(a, t + 5)
...followed by something to place the value of b into Range("B31:B40")?
 
Upvote 0
Code:
Sub test()
Dim a As String, b As String, t As Long
a = Range("A3")
t = InStr(a, "CHUNK")
b = Mid(a, t + 5)
Range("B31") = b
End Sub

although for 1 merged range, not sure why you don't just use a formula.
 
Upvote 0
That worked like a charm! There may be a more straightforward way, but as I mentioned, I'm definitely a rookie and will take what works.
Thanks for the help!
 
Upvote 0
This formula in your B31:B40 merged cell will give you the same result:

=REPLACE(A3,1,FIND("CHUNK",A3)+4,"")
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top