"In Between" function that behaves similarly to a "Mid String" function

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
In column A, I have a string within a cell with an alpha-numeric constant of 1A23 at the beginning and 432A1 at the end. In between could be any number of alpha-numeric characters. In column B, I need a formula that will extract the variable from the middle of these two constants in column A. The reason why I can't use the mid, left, or right functions is because the lenght of the variable could change from cell to cell.

-examle-

A1: 1A23abcd4A321
A2: 1A23abcdefg4A321
A3: 1A23ab4A321
A4: 1A23abcde4A321

B1: abcd
B2: abcdefg
B3: ab
B4: abcde


If possible I'd like to accomplish this without the use of VBA by strictly taking advantage of excel's built-in formula functions (if possible). However, if someone knows how to write this in vba, I'd be grateful to see how as well. :)
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
a formula like below should do the job even if the length of string in each cell is different

cell B1 =MID(A1,5,(LEN(A1)-4-5))

Avoid spaces in the start and end of strings
 
Upvote 0
You should just need one MID function, as long as the first four and last five characters are always there.

=MID(A1,5,LEN(A1)-9)
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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