Always seem to see some boffins on here.

So i have a list of co ordinates for a cad file. I can read this in to my file fine but what i would like to do is limit the amount thats read in. So i only need to populate based on a number.

So if i have 5 it will only populate the first 5 cells.

Hope that makes sense. It seems simple but i cant quite figure it out

Import the whole lot then delete everything from row 6 downwards?

I don’t know an easy way, but have a look at:

data > get data > launch query editor

then set what you want there.

that always makes my brain hurt, so unless it is reproducibly the first x rows you need, then I’d do what pfe says

edit: assuming a recent version…

There’s a function called MID which chops the centre figures of a string out. You can use it to do the first ones too. It’ll be something like

=MID(A1,0,5)

A1 is the cell where the coordinates are, 0 is the first number you want out of the string and 5 is how many you want. From memory, so may not be entirely right.

There’s probably a FIRST function or something too, think there’s an END but I don’t have a computer in front of me.

LEFT and RIGHT are the first and last functions tthew mentions but not sure they’re what you’re after.

Pete’s suggestion is simplest if I’m reading your requirement right.

Ah yes. Cheers dangeourbrian. When I read the OP again, I’m not quite sure I understand it now.

OK so basically I’ve got a offset and I can bring that into cad via a table link.
Think of it as a column of numbers. 1-10 if I put all ten in then the model will pull ten numbers through. However I might only want 5 depending on the calculation in the spreadsheet. So the list is fixed but the number of offset aren’t.

So I need a way of only populating 5 (or any number) of the results

Any reason you can’t have another column with an IF statement? You can do it with arrays, but it’s not overly easy.

So you have a cell somewhere with a number, say 5 (to use your example) in cell C1.

A1:A10 is your co-ordinates.

B1 is =If(count($B$1:B1)<=C1,A1,””), drag down, then column B will be populated for the first rows up to the number you specify in cell C1. You can then make your CAD program reference column B.

That assumes you will always want the first values though. If you want a varying subset of the data it’s harder, and I’d use an array.

Are you using OFFSET?

If so will OFFSET(a1,0,0,b1,1) not work to give a range starting at a1, value of b1 cells rows long, 1 column wide?

If you need to start at a specific value rather than a specific point, use MATCH to define the startpoint. I.E. OFFSET(a1,MATCH(c1,range,0),0,b1,1) where c1 is the value to start at and b1 is your number of values.

If I understand the question correctly then I would pull the full set into a separate tab within the spreadsheet and the just reference the first 5 or whatever.
I often do that for raw data and manipulate it in other ‘calculation tabs’

PM’d you, OP

My 1st PM on here – how excitment!!

Gents you are all both brilliant and amazing.

Rob hilton and nejee20 have both provided methods that work.

Made me happy this morning 🙂 I actually yelped at my desk 🙂

A little late to the game, but for this kind of data wrangling I usually use a script before importing into Excel – particularly if the amount of data you’re importing is significant.

actually while it worked it seems the cad program cant read a formula. Despite it being able to link via a variable to the spreadsheet it can actually only read a number in. Which is pretty shit if im honest

Are you pulling the raw data into cad then defining the data or defining the data (name) in excel then pulling to cad? Assuming the latter why is cad pulling a formula?

Cad tables can carry out formulas i can’t remember what ones.

I don’t understand the question. But i do know cad.