HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT

  • Thread starter Thread starter DENBRO
  • Start date Start date
D

DENBRO

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.
 
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.
Click to expand...

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)
 
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.
Click to expand...

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 said:
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)
Click to expand...
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
 
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)
 
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?
 
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)
 
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)
 
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
Click to expand...

Sorry I misinterpreted your question.

But it is still relatively simple:

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

--ron
 
Back
Top