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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This can be done without VBA:

=REPLACE(A1,1,FIND("CHUNK",A1)+4,"")
 

NMatt

New Member
Joined
Aug 24, 2007
Messages
17
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?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

NMatt

New Member
Joined
Aug 24, 2007
Messages
17
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")?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

NMatt

New Member
Joined
Aug 24, 2007
Messages
17
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!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This formula in your B31:B40 merged cell will give you the same result:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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