I've transformed my pdf statements into text and put into excel 2007. All
entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank numbers vary
in length. what I want to extract is the text for the payment info to the
right of space 3 to cut out all the lead in. I've tried several functions and
combinations but can't seem to hit it. Any help is appreciated. thanks.

To extract the text to the right of the third space from the left, you can use the combination of the RIGHT, LEN, and FIND functions in Excel. Here are the steps:

1. Assuming that the text string is in cell A1, use the FIND function to locate the position of the third space from the left. The formula would be:
   
   
   Formula:

   =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1) 

   This formula finds the position of the third space by first finding the position of the first space (using FIND(" ",A1)), then adding 1 to skip it, finding the position of the second space (using FIND(" ",A1,FIND(" ",A1)+1)), adding 1 to skip it again, and finally finding the position of the third space.
   
2. Subtract the position of the third space from the total length of the text string using the LEN function to get the number of characters to extract. The formula would be:
   
   
   Formula:

   =LEN(A1)-FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1) 

   This formula subtracts the position of the third space from the length of the text string.
   
3. Use the RIGHT function to extract the desired text. The formula would be:
   
   
   Formula:

   =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)) 

   This formula extracts the text to the right of the third space by using the number of characters to extract as the second argument of the RIGHT function.
   

Once you have entered this formula in a cell, you can copy and paste it to other cells to extract the text from other entries.

On Sat, 16 Jan 2010 11:16:01 -0800, DENBRO
wrote:

I've transformed my pdf statements into text and put into excel 2007. All
entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank numbers vary
in length. what I want to extract is the text for the payment info to the
right of space 3 to cut out all the lead in. I've tried several functions and
combinations but can't seem to hit it. Any help is appreciated. thanks.

Given that the text you want will always start in the 13th position, you could
simply use:

=MID(A1,13,255)

255 just needs to be larger than the longest substring that needs to be
extracted/
--ron

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:

I've transformed my pdf statements into text and put into excel 2007. All
entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank numbers vary
in length. what I want to extract is the text for the payment info to the
right of space 3 to cut out all the lead in. I've tried several functions and
combinations but can't seem to hit it. Any help is appreciated. thanks.

?B?REVOQlJP?= wrote in
:

I've transformed my pdf statements into text and put into excel 2007.
All entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank
numbers vary in length. what I want to extract is the text for the
payment info to the right of space 3 to cut out all the lead in. I've
tried several functions and combinations but can't seem to hit it. Any
help is appreciated. thanks.

if in A1
- you want to extract NAME OF BUSINESS LOCATION AMOUNT
- date is always 11 (i would say 12, if theres a space after the last date
- that 254WW17PGA84HM5V3 is a always random length (no spaces???!)
copy&paste this in B1

=MID(RIGHT(A1,LEN(A1)-12),FIND(" ",RIGHT(A1,(LEN(A1)-12)),1)+1,LEN(A1))

and is should just work

"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..

Ron, thanks for your assistance but your formula returns:
254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
I want to get rid of the bank number and just get
NAME OF BUSINESS LOCATION AMOUNT
Unfortunately, the bank number is never constant and changes in length. I
need everything to the right of the third space from the left. thanks for
your time and assistance. Denbro

"Ron Rosenfeld" wrote:

On Sat, 16 Jan 2010 11:16:01 -0800, DENBRO
wrote:

I've transformed my pdf statements into text and put into excel 2007. All
entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank numbers vary
in length. what I want to extract is the text for the payment info to the
right of space 3 to cut out all the lead in. I've tried several functions and
combinations but can't seem to hit it. Any help is appreciated. thanks.

Given that the text you want will always start in the 13th position, you could
simply use:

=MID(A1,13,255)

255 just needs to be larger than the longest substring that needs to be
extracted/
--ron
.

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..

Mike, you're quite the guru. that worked great, thanks. You seem to be very
knowledgeable. Any suggestions on books to teach functions and other cerebral
matters?

"Mike H" wrote:

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..

Glad I could help and thanks for the feedback.

If you want to improve your Excel knowledge then these forums aren't a bad
place to start. Have a go at answering questions or simply view the answers
and deconstruct the formula to see how they work.

With regard to books then John Walkenbach has written many and I would
recommend any of those.

Have a look at the website

http://www.j-walk.com/
--
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, you're quite the guru. that worked great, thanks. You seem to be very
knowledgeable. Any suggestions on books to teach functions and other cerebral
matters?

"Mike H" wrote:

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..

Given your dates **always** take up 11 characters and that they are always followed by a space, you can use this somewhat compact formula to extract what you want...

=MID(MID(A1,13,999),1+FIND(" ",MID(A1,13,999)),999)

--
Rick (MVP - Excel)


"DENBRO" wrote in message ...
I've transformed my pdf statements into text and put into excel 2007. All
entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank numbers vary
in length. what I want to extract is the text for the payment info to the
right of space 3 to cut out all the lead in. I've tried several functions and
combinations but can't seem to hit it. Any help is appreciated. thanks.

Try this:

=MID(A1,FIND(" ",A1;13)+1,99)

"DENBRO" wrote:

I've transformed my pdf statements into text and put into excel 2007. All
entries are similar to this:
12/16 12/18 254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
the two dates are always 11 characters but the next set of bank numbers vary
in length. what I want to extract is the text for the payment info to the
right of space 3 to cut out all the lead in. I've tried several functions and
combinations but can't seem to hit it. Any help is appreciated. thanks.

On Sat, 16 Jan 2010 12:31:02 -0800, DENBRO
wrote:

Ron, thanks for your assistance but your formula returns:
254WW17PGA84HM5V3 NAME OF BUSINESS LOCATION AMOUNT
I want to get rid of the bank number and just get
NAME OF BUSINESS LOCATION AMOUNT
Unfortunately, the bank number is never constant and changes in length. I
need everything to the right of the third space from the left. thanks for
your time and assistance. Denbro

Sorry I misinterpreted your question.

But it is still relatively simple:

=MID(A1,1+FIND(" ",A1,13),99)

--ron