![]() |
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. |
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:
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. |
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 |
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. |
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 |
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.. |
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 . |
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.. |
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.. |
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.. |
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. |
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. |
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 06:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com