# return value after a set number of character instances

#### nofrillz

##### New Member
Here is my problem...
I have simplified my excel pages as much as I can to make this issue easy but I am left with one last column I cannot complete.

I need to return only the values in a set number of cells in a larger range (always counting and including the cells starting from left to right) based on a number manually set in another cell, while the range is always static (lets say 20 cells)

so If I have the number 8 in one cell that I am going to reference in this formula as the "count" and I want to use that "count" ONLY to return the valuse of the first (8 in this case) cells in a range of lets say 20, what would that formula be?

If the number is 2 I want to then ONLY return the values in the first 2 cells in the same range of 20, etc. etc.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### PCL

##### Well-known Member
What do you want to do with the 8 or 2 first numbers.
If they need to be included in a formula ( SUM for example) then you could use an OFFSET function to cover the range for these 8 or 2 first numbers.
SUM(OFFSET(A1,0,0,COUNT,1)) NOT TESTED
Where COUNT is the cell containing = 8 or 2.

#### nofrillz

##### New Member
Let me try to better explain this (i'd post the cells but Im at work and have no access to install the plug in to convert to HMTL).

I have 5 size scales, 1 has 5 sizes, 2 has 6, so on and so on alway incrementing by 1 so that the 5th has 9. On an excel order form there are 9 cells in a row that hold the quantity of units. The quantiy is dependant on the size scale so if someone is using size scale 1, I might have the following rows 1,2,1,1,2,0,0,0,0 The trailing last 4 cells of zeros are there because in size scale 1 there are only 4 sizes to order therefore the last set of columns are empty.

This file is coming from a client and I cannot change it nor the way it is filled out.

My system expects ONLY the qty for the exact amount or size scale, so in my system is someone is ordering against size scale 1, my system needs "1-2-1-1".

So my dilema is that I can pass the size count to a formula based on other formulas I wrote, so Ill know "only return the values in the first 5 cells" however I dont know how to write a formula that says grab all 9 cells, cut it after my count, then merge the content seperated by a "-".

Hope this clears this up, if not Ill wait till I get home to paste the HTML in of my examples.

#### nofrillz

##### New Member
Indeed...I knew i was writing too fast.

#### nofrillz

##### New Member
Ill try this again...size scale 1 = 5 sizes sorry for the confusion

#### PCL

##### Well-known Member
To start quickly perhaps next UDF could help.
Assuming numbers to treate are in column somewhere:
G2:G10 = 1 2 1 1 2 0 0 0 0
Cell named COUNT = 5
I9 = MYRESULT(Count,G2:G10)
Code:
``````Option Explicit
Function MYRESULT(MyCOUNT As Integer, MyRG As Range) As String
Dim I As Integer
MYRESULT = MyRG.Cells(1, 1)
For I = 2 To MyCOUNT
MYRESULT = MYRESULT & "-" & MyRG.Cells(I, 1)
Next I
End Function``````

#### Scott Huish

##### MrExcel MVP
You could also instead of providing the count, provide the size and let the macro determine the count for that size:

Code:
``````Function ReturnCount(scalesize As Long, r As Range) As String
Dim i As Long, tmp As String
scalesize = scalesize + 4
For i = 1 To scalesize
tmp = tmp & r.Cells(i) & "-"
Next
ReturnCount = Left(tmp, Len(tmp) - 1)
End Function``````

So, for size 1
=ReturnCount(1,G2:G10)

Replies
2
Views
205
Replies
13
Views
803
Replies
6
Views
269
Replies
2
Views
110
Replies
9
Views
439

1,190,646
Messages
5,982,113
Members
439,755
Latest member
nicos18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back