Excel Advice please

[wesimmo]

Its things like this that make most Excel training only partially useful.

The big outcry recently about how dangerous Excel is a little unfair, it is dangerous they its used, but if you gave a child an assault rifle and showed them how to fire it, but didn't given them any lessons on gun safety, the results would be bad.

 

[Django x2]

Knew it would be a simple answer.

Now to work out a way of deleting that space in front of each time with hundreds of times across multiple worksheets .

Easy, select the column, select Data, text to columns, and choose space as the delimiter

 

[wesimmo]

Knew it would be a simple answer.

Now to work out a way of deleting that space in front of each time with hundreds of times across multiple worksheets .

You don't, get your import working correctly.

Or maybe long way round, just add a second column into your template that multiplies the text by 1, it might force Excel to convert them, then calculate off that column.

 

[wesimmo]

Easy, select the column, select Data, text to columns, and choose space as the delimiter

This works, but is manual every time, if I was designing a process, I'd want to automate that as its easy to do and reduces potential errors from manual intervention.

 

[Entai]

You don't, get your import working correctly.

So how can I adjust the way the data is imported ?

I have just been dragging and dropping.

Sorry if its a daft question.

 

[wesimmo]

Not at all, I've been doing time in Excel for 20+ years, so I've made more mistakes in Excel than most people, which is why I knew almost straight away what your issue was, been there myself!

Its hard to say without knowing the full steps you go through, but if you are already manually moving the data, the easiest way will probably be to copy>>paste values.

If not, like I say, just doing what you're doing, but then having a second column pre set up to multiply the values you drag in by 1 might work.

Ideally, though, find out where the data is first extracted, and make sure that the column for times is formatted correctly, or as @Django x2 suggested, the de-limiter might just need setting correctly to remove the spaces at the point of data extraction.

Edit: also, instead of multiplying by 1, use =value(A1) or whatever the cell is with the 'text numbers', forgot about that one!

 

[Django x2]

So how can I adjust the way the data is imported ?

I have just been dragging and dropping.

Sorry if its a daft question.

Another method, slightly less complicated than the text to columns is to use TRIM. We know that the leading space is output from the previous data source, so in Excel, in the adjacent column to any of the times, type =AVERAGE(TRIM(A1)) (where A1 is the cell with the time in), then drag that down to populate that value against all the times in the column, then run your =AVERAGE(A1:A12) again.

TRIM will only remove the leading whitespace, it's more complicated if you have mid whitespaces you want to keep or remove, but I don't think we have that here.

Best answer, find a way to sanitise that leading whitespace in the export of the data, or export it as CSV and when importing it to Excel use both space and comma as the delimiters.