excel表格怎么批量换行内容不同

1.excel怎样批量处理同一格的换行内容变成同一行?

=LEFT(B1,4)&"-"&MID(B1,5,5)&"-"&RIGHT(B1,4)

这个公式我一步一步来解释，left(B1,4)就是去B1单元格的前4个字符，mid(B1,5,5)就是取B1单元格从第五个字符开始，往后取5个字符（为啥是5个呢，因为换行了，多一个类似空格的字符，这个可以根据实际调节），right(B1,4)就是取B1右边的4个字符，&符号就是把取的这些字符连接成字符串

ps：公式里面的4、5这些数字，根据实际字符数来取，简单换一下看看效果就行了，多取了数值减去1，少取了加上1，很简单的

2.excel中,如何将一个单元格里的内容分拆成不同行

看了眼别人的公式，如果不是3位数就废了

我弄了一个公式挺麻烦的，不过可以保证不在乎你的数字到底是几位数，只要分隔符是逗号就好，一直可以

=MID(","&A$2&","&A$3&","&A$4&","&A$5&","&A$6&","&A$7&","&A$8&",",FIND("@",SUBSTITUTE(","&A$2&","&A$3&","&A$4&","&A$5&","&A$6&","&A$7&","&A$8&",",",","@",ROW()))+1,FIND("@",SUBSTITUTE(","&A$2&","&A$3&","&A$4&","&A$5&","&A$6&","&A$7&","&A$8&",",",","@",ROW()+1))-FIND("@",SUBSTITUTE(","&A$2&","&A$3&","&A$4&","&A$5&","&A$6&","&A$7&","&A$8&",",",","@",ROW()))-1)

上图看结果~：

我又做了个宏，可以适应更多的数据量，看上去也比公式更直观，易于修改

Sub aa()

Dim aaa(100) As String

ccc = "，" '分割符号在这里修改

'连接所有字符

abc = Cells(2, 1).Value

For i = 3 To Range("a60000").End(xlUp).Row + 1

abc = abc & ccc & Cells(i, 1).Value

Next

b = 1

For m = 1 To Len(abc)

p = Mid(abc, m, 1)

If p <> ccc Then

aaa(b) = aaa(b) & p '分割字符

Else

Cells(b, 2).Value = aaa(b) '输出到单元格

b = b + 1

End If

Next

End Sub