Excel - How to extract data from a string of information? 7

[wec43wec]

In Excel - I have a file containing over 2,000 rows which has "within column "A" on each row a number of data elements that are not the same length which I need to separate.

Note: The data within each row is listed in the following order -

1. the address (differnet length/number of characters)
2. the city name (6 possible)
3. the gas tank capacity (amount of fuel)
4. the time of day


The format/sample of the data on each row is as follows:

1700 Penn Ave NE Washington 1/4 full 12:00
123 Main ST NW Baltimore Full 10:00



1. I need to know if I can pull the "address" from cells A1 thru A2000 (which contains the string of data) into a second cell (B1 thru B2000) by saying I want everything to the left of the "city name". Note: There are 6 different names of cities, but I could use the same formula 6 times by changing the city name or inserting some type of if statement.

2. Each row also contains the volume within a gas tank and has 5 different possible felds ("Empty", "1/4 Full", "1/2 full", "3/4 full" or "full"). Question - I need to show which of these 5 elements are on each row from data contained in xolumn "A").

Any help with any of the 2 questions would be most helpful.

Thanks

 

[macropod]

Hi wec43wec,

For a record in A1, try:
=LEFT(A1,MIN(IF(NOT(ISERROR(FIND({" empty"," 1/4"," 1/2"," 3/4"," full"},A1))),FIND({" empty"," 1/4"," 1/2"," 3/4"," full"},A1)))-1)


Cheers
[MS MVP - Word]

 

[Gavona]

That solution appears to be case sensitive. So you need to add " Full"

For the second query:

If macropod's formula is in column B then this in column C will return the rest of the text:
=SUBSTITUTE(A1,B1&" ","")
Copy this to values (Edit, Copy Edit, Pastespecial, Values)
Then use Data, text to columns with space as a delimiter to get the remaining data split into columns.

Gavin

 

[macropod]

That solution appears to be case sensitive. So you need to add " Full"

If you want to avoid the case issues, a better approach would be to use SEARCH instead of FIND:
=LEFT(A1,MIN(IF(NOT(ISERROR(SEARCH({" empty"," 1/4"," 1/2"," 3/4"," full"},A1))),SEARCH({" empty"," 1/4"," 1/2"," 3/4"," full"},A1)))-1)

You can also extract the time with:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

So, if you've got the address formula in column B and the time formula in column D, you can get the fuel level in column C with:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""))


Cheers
[MS MVP - Word]

 

[wec43wec]

Macropod - this is just EXCELLENT !!!!!!!!!!!!!!!!!!

This may be asking a bit too much, but i need to study and fully interpret this formula b/c as of now, it does the trick but I do not understand how it works.

Gavona - your formula also work, but I need to study it also in that I do not know how it does the trick.

Both of you guys are excellent and should have more then one star.

Thanks

 

[wec43wec]

Macropod - why are brackets used in your formula?

LEFT(A1,MIN(IF(NOT(ISERROR(SEARCH({" empty"," 1/4"," 1/2"," 3/4"," full"},A1))),SEARCH({" empty"," 1/4"," 1/2"," 3/4"," full"},A1)))-1)


Also, why must the "-1" be used at the end of the formula?

 

[macropod]

Hi wec43wec,

If you're referring to the braces (ie '{}'), they define the array of values the FIND/SEARCH function is testing. As for the '-1', that simply tells the LEFT function to terminate at 1 character less than the value returned by the MIN of the FIND/SEARCH function results.


Cheers
[MS MVP - Word]

 

[wec43wec]

Macropod - thanks for the response. I now understand the "-1", however I always thought that to initial an array, you must hit the "enter, control and shift" key at the same time.

 

[macropod]

Hi wec43wec,

An array of constants within a suitable formula as in this case doesn't require Ctrl-Shift-Enter. See 'Array formulas and how to enter them' in Excel's help file.


Cheers
[MS MVP - Word]

 

[kjv1611]

Chalk it up to another tek-tips day. I just learned something new - "an array of constants..

 

[SkipVought]

Hip, hip...
[purple]
ARRAYYYYYYYYYY!!! ==>*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougP]

macropod, you rock

amazing one-liner of the year!!!!


DougP
< I Built one

http://www.robots2000.com

 

[xlhelp]

One more.

You can probably have your own Constellation with just this thread.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

[tt][purple]
*
*
*
*
*
*


*


*[/purple]
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Thanks folks.


Cheers
[MS MVP - Word]