Retrieve text from column

[J.W. Aldridge]

Hi.
I am wondering if the following is possible and if anyone could
assist....

I have data that I am pasting into excel (a1). I cannot adjust the data
(using text to columns) as the data may be in different places but
always on the same row.

Example: All of the following data will be in one column (a1)....

Name Joe Blow Sr. Address 1234 MIckey Mouse Lane #4
City Nowhere State Florida
Co Name Cold Sizzlers Phone (555)555-4321

I am lookin to retrieve the next 14 characters after the word "name" is
found.
I am looking ot retrieve the next 12 characters after the word "State"
is found.

etc....

Ultimately I want to search one column for a keyword and retrieve the x
number of characters afterward.

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi.
| I am wondering if the following is possible and if anyone could
| assist....
|
| I have data that I am pasting into excel (a1). I cannot adjust the data
| (using text to columns) as the data may be in different places but
| always on the same row.
|
| Example: All of the following data will be in one column (a1)....
|
| Name Joe Blow Sr. Address 1234 MIckey Mouse Lane #4
| City Nowhere State Florida
| Co Name Cold Sizzlers Phone (555)555-4321
|
| I am lookin to retrieve the next 14 characters after the word "name" is
| found.
| I am looking ot retrieve the next 12 characters after the word "State"
| is found.
|
| etc....
|
| Ultimately I want to search one column for a keyword and retrieve the x
| number of characters afterward.
|

 

[J.W. Aldridge]

PERFECT.....


THANX!

 

[David McRitchie]

Hi Jeremy,
Looks like you could change your data with Ctrl+H so that you
could use Text to Columns using a delimiter

Change all " Address " to ","
Change all " State " to ","
Change all " Co Name " to ","
Change all "Phone (" to ",("

If you already have commas in your data use a semi-colon
as the delimiter.

After you have your data in columns, you can remove
extraneous spaces with the TrimALL macro see
http://www.mvps.org/dmcritchie/excel/join.htm#trimall



"Niek Otten" wrote...