如何获取列中最长字符串的长度(Excel)? [英] How can I get the length of the longest string in a column (Excel)?

问题描述

我有一个要写入文件的Excel电子表格.我目前正在使用以下方法重现每个单元格的内容:

I have an Excel spreadsheet that I am writing out to a file. I am currently using the following method to reproduce the contents of each cell:

cell_contents = Right(Space(10) & Trim(Cells(iRow, iColumn)), 10)

但是，如果单元格的内容长于10个字符(或者我选择指定的长度)，那么我将丢失部分数据.有没有一种方法可以快速轻松地获取一系列单元格中最长字符串的长度?

However, if the contents of the cell are longer than 10 characters long (or however long I choose to specify), then I loose parts of the data. Is there a way to quickly and easily get the length of the longest string in a range of cells?

我已经看到人们建议使用以下循环，但是我希望我可以不必遍历所有单元格就能做到这一点:

I have seen people suggest using the following loop, but I was hoping that I might be able to do it without having to loop over all the cells:

For Each c In SourceRange.Cells
   if len(c) > b then b = len(c)
Next c

推荐答案

将这些步骤记录为要计算最大长度的单元格中的宏.

Record these steps as a macro in the cell you want to calculate the max length.

1)输入此公式

=MAX(LEN(A1:A4), 1) -- Edit for your range.

2)press control-shift enter(FormulaArray)

2) press control-shift enter (FormulaArray)

3)现在停止录制宏

4)查看VBA.将您需要的内容复制到您的VBA.

4) view the VBA. copy what you need to your VBA.

应该给你这样的东西:

Selection.FormulaArray = "=MAX(LEN(R[-10]C[1]:R[-1]C[1]))"

这篇关于如何获取列中最长字符串的长度(Excel)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！