Question - Working with Covers ATS data in excel

The records are laid out in a single cell

i.e. "23-2-8" and "24-8-1"

if i wanted to add up a teams record over multiple years

i.e. 23-2-8 + 24-8-1 = 47-10-9

would there be a formula for this that would isolate and add each value independent of the others. Finally, does it matter if i have it formated as a text. Bloody excel switches some of the records automatically to dates as soon as i paste the data.

Thanks

If there's a way to do that, I don't know of it. I believe Excel can only add cells together. I use separate cells for each number. You won't need to format as text anymore, either.

You can write some simple VB (visual basic) code that can manipulate string values in cells and place them independently in seperate cells in Excel. Excel supports VB and you can use the editor by going to Tools>Macro>Visual Basic Editor. There are tons of references online to create this kind of code and VB is probably the easiest language to write code in.

 

As for the formatting, you should select all the cells on sheet before import and set the format to Text for all of them. When you paste, right-click and select Paste Special... and select Values. This should allow the actual text to be displayed in the cells.

 

Our company has done thousands of lines of code within Excel to manipulate alot of data in a short amount of time. I would recommend becoming familiar with VB within Excel if you are using it for data manipulation. You can process millions of lines of data with several lines of code in seconds.

Thanks this will take some time to figure out but atleast i have some direction now!

after you get the data in you can highlight the column that has 23-8-4 in it, then choose Data menu and then Text to Columns.  Choose - as the delimiter and it will break each out into its own column.

Thanks, that worked.