Mid function

[nygwnj]

is there some variant of the Mid function that returns a characters to the left of the starting position intead of to the right?

I know I can find the starting point of a sub-string then strip off that sub-string and everything to the right of it, then simply use the Right function to obtain what I'm looking for, but I thought a Mid function that runs to the left would be easier.

Thanks,

Gary Willick

[stanleydgromjr]

nygwnj,

Can we have an example of the string, and what the function should return?

[nygwnj]

I just realized I can use the Left function up until the spot I found my sub-string - 1 (to allow for a space), then use the right function to obtain my desired sub-string.

The string would look like:

Willick 07646 batch 4678

So I could find the position of the 'b' in batch, subract 1, use the Left function to that position to obtain:
Willick 07646
then
use right to obtain the zip code, 07646.

but I'm still interested in a leftward proceeding Mid function.

[gjlindn]

I bet there are a dozen or so ways to get the results you're looking for, but I have never seen a simple native VB function that would take the characters to the left...I'll keep watching this post in case someone comes up with the answer. I'm curious and hopeful

[Armando Montes]

Try this:
=MID(A2,FIND("b",A2)-6,5)

[stanleydgromjr]

nygwnj,

If this is in cell A1:
Willick 07646 batch 4678

In B1 copy this formula:
=MID(A1,FIND(" ",A1,1)+1,FIND(" batch",A1,1)-1-FIND(" ",A1,1))

B1 will display:
07646

[gjlindn]

@Armando:

Try this:
=MID(A2,FIND("b",A2)-6,5)

I like it

[NBVC]

If you are looking for the second substring (or "word") try:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

[stanleydgromjr]

NBVC,

Nice....

If we were looking for the third substring, what would your formula look like?

[NBVC]

Like so:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))

[stanleydgromjr]

NBVC,

Thank you again.

Now I understand how the formula works.