| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract Text with Left, Mid, etc | Excel Worksheet Functions | |||
| Extract name before comma, space & number | Excel Discussion (Misc queries) | |||
| How to extract left-most number from a string | Excel Worksheet Functions | |||
| Need to pick up data from left of a space starting from the right? | Excel Discussion (Misc queries) | |||
| space left for future entry = #DIV/0! | New Users to Excel | |||
Hybrid Mode
