Hi,

I misunderstood and what you want is a lot simpler

=TRIM(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3)),LEN(A1)))

--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)


"DENBRO" wrote:



"Mike H" wrote:

Hi,

If your data are consistent then this will work

=MID(MID(MID(SUBSTITUTE(A1," ","^",2),1,256),FIND("^",SUBSTITUTE(A1,"
","^",2)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1,"
","^",2),1,256),FIND("^",SUBSTITUTE(A1," ","^",2)),256))-2)

--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)


"DENBRO" wrote:

Mike, thanks for the reply. Your formula returned the bank number. In the listed example it was 254WW17PGA84HM5V3. However what I'm trying to get is the
NAME OF BUSINESS LOCATION AMOUNT. I appreciate your time and assistance..