excel

[atchaya17]

               I have value in each cell. I want to add comma at the end for example Structured QUery Language, Science, Social. So I can count each cell value with the help of comma. How to add comma I have 20000+ columns and each cell has 20 to 30 values.Please let me know

[#2]

@atchaya17 

Hello atchaya17,

I hope the attached example document is what you are looking for. Otherwise, it will give you the inspiration you need to find your solution.

=SUBSTITUTE() is the solution to your problems.

best regards

dscheikey

[#3]

Hi, Can I know what syntax you have used?

[#4]

I got the answer, Thank you so muchhhhh

[#5]

also if i have blank in my cell how to change the syntax

[#6]

Should existing blank lines in the cells remain? Please describe exactly what you want as an outcome and what the final result should be. This is the only way to help.

[#7]

I mean , each cell there was  5 to 6 values when I used for formula it worked, but in some cells it has empty cells, When I used for syntax it added as comma in the empty cell @dscheikey in this example 2nd cell dont have any values but it taken as comma.

[#8]

@atchaya17 

A small IF() formula helps here. See the example document.

[#9]

Worked out. I am learning something so new to me.Thanks for the kind help.Happy friday

[#10]

@dscheikey   but I getting comma at the end of the line too

[#11]

@atchaya172045 

I added the comma because you wanted to count the rows in the cell using the comma. I have now taken it away and added a plus 1 to the count in column C.

[#12]

thank you

[#13]

@dscheikey  hi I  used your syntax to get the answer .but in one cell in my 1st column I have 54 values, I have used formula for 2nd column to count the skills ..in 2nd column it has 51 skills , but I got answer as 54. Which is taking data from 1st column.I want count of valued from 2nd column as  51.can you let me know.

[#14]

@atchaya_16 

If you don't get the right result, it could be because there are commas in your text. You can also count the line breaks. This should lead to the correct result.

=IF(B1="";"";LEN(B1)-LEN(SUBSTITUTE(B1;CHAR(10);""))+1)

Otherwise, please upload the document (at least the affected line).

dscheikey

[#15]

@dscheikey  just now used this  syntax.now I got exact count. And can you tell me why char 10 is used .I'm new to this excel .could you explain this code.

[#16]

From the number of characters in the original text, the number of characters where the line breaks were replaced by nothing is subtracted. The result is the number of line breaks (CHAR(10)). Since there is no break after the last line, a one is added.