Spreadsheet help

Latest Articles

Spreadsheet help
Page Previous 1, 2

Downsizer Forum Index -> IT Matters

Author

Message

RichardW

Joined: 24 Aug 2006
Posts: 8443
Location: Llyn Peninsular North Wales

Posted: Fri Feb 21, 14 3:00 pm Post subject:
no But as they now point to another cell even if that cell is empty they populate with a 0 anyway.

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Fri Feb 21, 14 4:52 pm Post subject:
While we're on it, I have another question - I'm using an Excel spreadsheet of weights data, but the values are entered as "7.1 kg" - is there a away of keeping just the number? By means of a formula, that is, not manually!

Nick

Joined: 02 Nov 2004
Posts: 34535
Location: Hereford

Posted: Fri Feb 21, 14 6:36 pm Post subject:
Tell the cell it's a number?

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Fri Feb 21, 14 7:19 pm Post subject:
Tried that...

oldish chris

Joined: 14 Jun 2006
Posts: 4148
Location: Comfortably Wet Southport

Posted: Fri Feb 21, 14 7:29 pm Post subject:

Donkey's years ago, I did something similar, way back when Office 97 was the Bees' Knees. I checked using Libre Office Calc:

Assuming that the weights are in a column, formatted 7.1Kg. Highlight the column, goto Data, from drop-down select "text to columns", separated by "other", type "K" into box, I ended up with two columns, one with numbers, the other with a "g", the "K" got lost!

If you can work out what I mean.

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Fri Feb 21, 14 9:09 pm Post subject:
Yep, that will do it. Make a file with just the weights in a single column. Export the file and tell it you want to delineate the columns with a k. Then import it and you should have two column, one with the number, one with the g of kg. You might then need to use TRIM to clean up trailing spaces before it will let you change the format to numeric, not sure if it will do that automatically.

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Fri Feb 21, 14 9:28 pm Post subject:
Thank you - I'll try that tomorrow

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Sat Feb 22, 14 4:20 pm Post subject:
I can't find data or export - Microsoft appear to have made everything worse

oldish chris

Joined: 14 Jun 2006
Posts: 4148
Location: Comfortably Wet Southport

Posted: Sat Feb 22, 14 5:20 pm Post subject:

Rob R wrote:

I can't find data or export - Microsoft appear to have made everything worse

all to do with knowing what words to use! The key words that I used to seek help were "Excel+split+column+text+delimiter" and I found this: https://office.microsoft.com/en-gb/excel-help/split-a-column-of-text-HA103994003.aspx

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Sat Feb 22, 14 5:32 pm Post subject:
Would you like to email it to me? I have a spare ten minutes that I need to fill with something rather than doing what I should be doing

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Sat Feb 22, 14 5:35 pm Post subject:
Consider it sent - it was actually part of my ten minute procrastination, when I'm supposed to be packing meat

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Sat Feb 22, 14 5:37 pm Post subject:
Right, I've got it - do you just need the single column of numbers sending back?

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Sat Feb 22, 14 5:45 pm Post subject:
Yes, as long as you don't jumble them up Although it's only a test, with weights of the baby, me, or combinations of the two, so no real issue if they are jumbled.

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Sat Feb 22, 14 5:47 pm Post subject:
Thank you

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Sat Feb 22, 14 6:19 pm Post subject:
No worries. When I imported it, I needed to tell it to use a comma as column delineation as well as the other things.

	Downsizer Forum Index -> IT Matters	All times are GMT Page Previous 1, 2
Page 2 of 2

View Latest Posts