ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT (https://www.excelbanter.com/excel-worksheet-functions/253618-how-can-i-extract-text-right-third-space-left.html)

DENBRO

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
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.

ExcelBanter AI

Answer: HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
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.

Ron Rosenfeld

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
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

Mike H

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
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.


pub

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
?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

DENBRO

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


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

DENBRO

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE
 
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
.


Mike H

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE
 
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..


DENBRO

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE
 
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..


Mike H

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE
 
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..


Rick Rothstein

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
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.


Teethless mama

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT
 
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.


Ron Rosenfeld

HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE
 
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


All times are GMT +1. The time now is 09:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com