kiwilondon99
Blogger
Share this content
0
793

excel function

excel function

have the following data extracted & now sitting in excel all in one cell

tue 06 oct 2015 05:45.00 pm utc

wed 07 oct 2015 04:22.00 am utc

would like to really have the data structured  as [ day] [ date ] [ time ] [am/pm] [ utc]

in order to do analytics on a very significant volume !!

the other fields of data have come across ok / useable - its just the above is all in one column

**  help / guidance from power users much  appreciated **

Replies

Please login or register to join the discussion.

24th Feb 2016 14:49

Text to Columns

You can highlight that column and then go to Data > Text to Columns.

Select 'Fixed width' and then Next, and you can then select where you want the column dividers to be. Based on what you have posted you will have the same number of characters for each column that you want.

This process doesn't work if there is any variability from row to row with respect to the space between the data items.

If you already have data in the columns to the right of this one you may need to move them far enough away so they don't get overwritten (although I'm not sure, I haven't used this function for years!)

Hope that works for you!

Thanks (2)
avatar
24th Feb 2016 15:46

Text to Columns

michaelbeaver wrote:

If you already have data in the columns to the right of this one you may need to move them far enough away so they don't get overwritten (although I'm not sure, I haven't used this function for years!)

That is correct, although Excel will prompt you beforehand to ask if you wish to overwrite the existing data.

Thanks (0)
avatar
25th Feb 2016 14:08

simplest to do..

 

@michael

many thanks for this - i have understood what was needed and it worked with a good chunk of data,. but it is as you stated re overwrite  - so just inserted enough  empty colums and  adjusted formating of the initial ColA   Just the reformat of the  'time' column - coudnt do that from the  convert box - but able to reformat column to the required format after all converted

So many thanks

Thanks (0)
24th Feb 2016 15:17

Use Find & Replace then TextToColumns

to find:

<space>am<space> and replace with;am;<space>pm<space> and replace with;pm;<space>year<space> and replace with;year;repeat 3 for all years in the data

In the column for the right of your data use the formula:

=SUBSTITUTE(Data cell reference," ",";",1)

Copy & Pastespecial values the column with the SUBSTITUTE formula in, then use Data TextToColumns for delimited data with the delimiter set as ; (semicolon)

That should insert semicolons in all the places you need to split  with Text To Columns and get you around having to use Fixed Width in stead of Delimited

Thanks (3)
By tom123
24th Feb 2016 16:05

I like that

Paul D Utherone wrote:

to find:

<space>am<space> and replace with;am;<space>pm<space> and replace with;pm;<space>year<space> and replace with;year;repeat 3 for all years in the data

In the column for the right of your data use the formula:

=SUBSTITUTE(Data cell reference," ",";",1)

Copy & Pastespecial values the column with the SUBSTITUTE formula in, then use Data TextToColumns for delimited data with the delimiter set as ; (semicolon)

That should insert semicolons in all the places you need to split  with Text To Columns and get you around having to use Fixed Width in stead of Delimited

Paul - I like that!

I often need to do similar activities, and this, as you say, gets round the fixed width problem.

Thanks (0)
24th Feb 2016 15:40

Or

... just do what Paul D said  :)

Thanks (1)
avatar
24th Feb 2016 17:59

An alternative approach

is to use Text to Columns using space as a delimiter then use DATEVALUE (e.g. DATEVALUE(b1&c1&d1) and for the time something like =E1+if(F1="pm",0.5,0) to create the dates and time in a useful format.

If you have access to Power Query, it would be possible to set this up so that you just refresh when new data becomes available. It might also help in ensuring the other columns of data are usable and that you only bring in those bits you need to work on (if the data volumes are large this may aid memory usage and speed).

 

Thanks (0)
avatar
24th Feb 2016 18:52

When you've done that

you may find future copy and paste operations don't seem to work, well, that's what I found.  So log out of Excel and log back in, and all should be well

Thanks (0)
By pacta
25th Feb 2016 12:18

Power Query

If the data is significant as you say you'll be wanting to keep the file size as low as possible. 

If you do your Text To Columns functions in Power Query and then group/truncate as necessary (e.g. do you really need Time AND am/pm? - unique timestamps will take masses of processing power and oftentimes are not needed).

Depending on the size of the dataset you may be wise to install Excel 64-bit as it does not limit the workbook's memory usage (which is 2GB with 32-bit I believe).

Thanks (0)
avatar
By paul.k2
28th Feb 2016 20:55

Flashfill

Have you tried Flashfill?

i dont think I have tried it with something just as complicated as this, but have been able to split up name fields etc.

Along with PivotTables it should be every accountants best friend :-)

 

Thanks (0)
Share this content