# CHAR Function

#### Krishna2385

##### New Member
Hi,

I have a daily activity which requires me to update the company names which are in separate cells into one cell based on the count. for example : if the count is two I need to combine company 1 and company 2 in one cell , like wise for count 3 and so on.
the data is huge the challenge for me is to add up the formula for the count more than 5. the count varies from data to data and it is not mandate that count 2 is followed by 3 nd 4 , if the data is not there it will skip 3&4 and updates 5 .
the formula which am using to update count 2,3 & 4 are as follows:
=C2&CHAR(10)&C3
=C3&CHAR(10)&C4&CHAR(10)&C5
=C4&CHAR(10)&C5&CHAR(10)&C6&CHAR(10)&C7
as I mentioned if its a sequential order I would have updated a simple macro but the count is not stable and may vary

#### Attachments

• 249.2 KB Views: 6

#### Chihiro

##### Excel Ninja
What version of Excel do you have?

Edit: Oh, and what you have currently is the desired result?

#### Krishna2385

##### New Member
2010 is the version....

yes the sheet which I have uploaded is the result I am looking for

#### Chihiro

##### Excel Ninja
Hmm, can't think of smart way of doing this without later version of Excel (CONCAT function introduced in Excel 2016 to replace old CONCATENATE).

Also, let me see if I understood your operation correctly. For each row, using Count in Column C, you concatenate x number of cells from each row? I ask, since, I can't think of reason for doing this.

At any rate, don't think I can help you with formula. Are you ok with VBA solution for this?

By the way, why are you concatenating CHAR(10)? If you want to do line break, it's best practice to use both CHAR(13) & CHAR(10) together. As some editor will not interpret CHAR(13) or CHAR(10) alone as line break. But both together, (vbCrLf/vbNewLine), will result in new line feed.

#### Krishna2385

##### New Member
Well VBA works for me.... and the reason to use CHAR(10) is in one our application there is a function which works only if I use CHAR(10). and it is done back end. and I cannot ask to use both CHAR(13) & CHAR(10).

#### Chihiro

##### Excel Ninja
Try this code.
Code:
``````Sub Demo()
Dim cel As Range
Dim y, x As String

For Each cel In Range("C7:C" & Cells(Rows.Count, 3).End(xlUp).Row).Cells
ReDim y(1 To cel.Value)
For i = 1 To cel.Value
y(i) = cel.Offset(i - 1, -1)
Next
x = Join(y, Chr(10))
cel.Offset(, 1).Value = x
Next
End Sub``````

#### Krishna2385

##### New Member
Awesome......... its works perfectly

Thanks you so much

#### Krishna2385

##### New Member
can anyone get a excel formula for the above query.... just to enhance my logical thinking in excel..

#### Krishna2385

##### New Member
Try this code.
Code:
``````Sub Demo()
Dim cel As Range
Dim y, x As String

For Each cel In Range("C7:C" & Cells(Rows.Count, 3).End(xlUp).Row).Cells
ReDim y(1 To cel.Value)
For i = 1 To cel.Value
y(i) = cel.Offset(i - 1, -1)
Next
x = Join(y, Chr(10))
cel.Offset(, 1).Value = x
Next
End Sub``````

I am able to get the results when I use this code however there is a Run time error '9' and it says Subscript out of range

when I searched it said if we don't specify the number of elements we get this error however in the above code ReDim is used and as per my research we shouldn't receive when we use Dim or ReDim.

#### Krishna2385

##### New Member
and to add further I have two sets of records in the same sheet for which I need to use the same code.

so here in my sheet I have the Count from M5 and count data from S5.

I have updated changed the range from "C7:C" to "M5:M" . as I mentioned earlier the code is working perfectly fine, I have re checked it there is no issue with data but still I receive the error.

and one more help ... how can I specify range for two different cells in same code. I have count from M5 which has to be updated and on S5. will update a sample file to clear this confusion.

#### Chihiro

##### Excel Ninja
I can't replicate your issue on my end. Please upload sample file where the error is produced.