Selecting Data using Excel

50Twuncle

I have been asked to try to find a way of "separating" a specific type of (numerical) data from a list - I have exported the list to excel but am now stuck - The data that I am trying to seperate is a constant format but appears at different points in each line of data

Any suggestions as to how to proceed ?

bchfc

Hi, This may help. If not if you can post and example of the data it may help find a solution. The formula Mid allows you extract some data from a string of text or numbers.

The example below, shows how it works.

Cell C2 Data = "123453"

Formula=MID(C2,2,3)

Result= 234

Not sure if this is what you are trying to do. Other options include pivot tables and sort functions. It depends on how your data is sorted.

Sambucus_Nigra

Can you use a pair of scissors?

bchfc

Yes if the list is not too long you could, cut and paste the data you want.

50Twuncle

Here is a made up example of the sort of data that I am looking at....

08:14 a.beck 128.543.87.75 xxxxxxxxxxxxxxxx yyyyyyyyyyyyyy red bhggfrrDSWEERTT5 fuffjfgoog


08:43 b.callow blue ggreswt55433gg99ig xxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzz 128.898.89.98


12:43 xxxxxxxxxxxxxxxxxxxxxxxxxx 8766tfdswe4377667f88ff8 bhgggririyiryiri nttototoot yyyyyyyyyyyy otttoto zzzzzzzzzzzzzzzzzzzottotoot 127.987.87.87 c.gettisbury yellow


What I am trying to do is to produce a report to line up all the individual (different lengths) of data
ie) Time, Name, Colour, ip address and Data – but also to remove a lot of unwanted rubbish (xxxxxxxxxxxxxxxxxxxx, yyyyyyyyyyy and zzzzzzzzzzzz)

Sambucus_Nigra

Are these bits of data in separate columns?

50Twuncle

Sambucus_Nigra wrote: »

Are these bits of data in separate columns?

Nope - they started life as a text file - and I have simply imported them in to Excel - I tried copying and pasting and ended unreadable - he columns were all over the place !
The list is too long to manually "play with" - over 400 rows !

Sambucus_Nigra

You might need to play with the delimiter that is used; to get the data to go into columns, and from there use the 'sort' function a few times to tidy up the data.....

Google 'importing data lists into excel'.....

bchfc

Thanks, how many rows of data ?

It looks like the data is not always in the same order in the cell ?

If so this make it tricky and a more manual exercise.

The mid command should work ok for time. It may also help you on name if the name is always in the same location in the cell.

For colour you could add a filter to the column and then filter on each colour in the cells, one by one. After you filter add the colour you filtered into another cell at the side of the original data. This may also work for the other data that you are trying to extract.

To filter on the data, highlight the data and go to the menus. Select data, filter autofilter. Then pick customer from the drop down menu and in the cell next to equals enter *red*, to filter on all red in the cells.

If you can change the format of the data imported using the delimitor that woudl also help. Especially if you can excluded the data you don't want.

Let me know if this is any help.

50Twuncle

bchfc wrote: »

Thanks, how many rows of data ?

It looks like the data is not always in the same order in the cell ?

If so this make it tricky and a more manual exercise.

The mid command should work ok for time. It may also help you on name if the name is always in the same location in the cell.

For colour you could add a filter to the column and then filter on each colour in the cells, one by one. After you filter add the colour you filtered into another cell at the side of the original data. This may also work for the other data that you are trying to extract.

To filter on the data, highlight the data and go to the menus. Select data, filter autofilter. Then pick customer from the drop down menu and in the cell next to equals enter *red*, to filter on all red in the cells.

If you can change the format of the data imported using the delimitor that woudl also help. Especially if you can excluded the data you don't want.

Let me know if this is any help.

On average 400 rows - and this needs to be done on a daily basis - hence my need to "automate" the procedure
And - as you point out - the data is not always in the same position
I will try to filter and a delimitor

johnmc

It looks like there is a space between each element.

Two options;
1 Save the data as a text file (use Notepad). Open Excel and then select File, Open (all file types) and point at the data. You should get the text import wizard. Select delimited and Next.

Choose the type of delimiter (in this case "space").


OR;
Highlight the column and select Data, "Text to Columns".