如何在excel公式中的每个字母后插入逗号 [英] how to insert comma after each letter in excel formula

问题描述

大家好，

你能不能请任何人帮我解决下面的问题。



如何在每封信后插入逗号（使用excel公式的单元格中的无限字符，不使用宏。



例如，

实际文字：EOAACADA

我需要输出：E，O，A，A，C，A，D，A

解决方案

如果不使用宏是绝对必要的那么你'开始玩得开心......



您可以使用SUBSTITUTE工作表功能将A替换为A等，但功能很快就会得到非常长......

替换（替换（替换）（替换（替代（替代（替代（替代（B4，A，A，），B） ， B，）， C， C，）， d， d）， E， E）， F， F）， G ，G，），H，H，）

大约是Excel 2002抛出错误之前所能达到的。



所以你必须有隐藏的单元格进行临时替换。



我设置单元格A4中包含以下文本的工作表

 ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 

然后将以下公式放入单元格B4到I4

 = SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE）（SUBSTITUTE（SUBSTITUTE（A4，A，A，），B， B）， C， C，）， d， d）， E， E）， F， F）， G， G，），H，H，）
 = SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（B4，I，I，），J）  J）， K， K）， L， L）， M， M）， N， N）， ○ ，O，），P，P，）
 = SUBSTITUTE（替换（替换（替换）（替代（替代（替代（替代（C4，Q，Q））， R， R，）， S， S，）， T， T）， U， U）， V， V）， W，W，），X，X，）
 = SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE）（SUBSTITUTE（SUBSTITUTE（D4，i，i，） ）， J， J）， K， K）， L， L）， M， M）， N， N， ），o，o，），p，p，）
 = SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SU） BSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（E4， 一， 一个，）， B， B，）， C， C，）， d， d）， e，e，，f，f，），g，g，，h，h，）
 = SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（ SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（F4， q， q）， R， R，）， S， S，）， T， T， ），你，你，），v，v，），w，w，），x，x，）
 = SUBSTITUTE（SUBSTITUTE（ SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（G4， Y， Y）， Z， Z，）， Y， Y）， Z，Z ，），0，0，，1，1，），2，2，），3，3，）
 = SUBSTITUTE（ SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（G4， 4， 4，）， 5， 5，）， 6， 6）， 7， 7，），8，8，），9，9，）

答案你需要的是第一列，你需要隐藏B到H列包含



你可以看到这真的很糟糕。



另一种选择是写一个加载项 - 这个链接会给你一个起点 HTTP：//www.pcmag .com / article2 / 0,2817,715709,00.asp [ ^ ]您可以使用类似于

 函数的函数 AddCommas（s  As   String ） As  字符串 
 
  Dim  i 作为 整数 
  Dim  sNew 作为 字符串 
 sNew =   
 对于 i =  1  要 Len（s）
 sNew = sNew + Mid（s，i， 1 ）+  ， 
 下一步 
 AddCommas = sNew 
 结束 功能 

< blockquote>为什么不使用搜索和替换功能。很容易，这就是我所做的，

搜索所有或项目，如果它的AABBCCDD然后替换为A，A，B，B，C，C，D，D



无需编程。

Hi All,
Can you please any one help me on the below one.

How to insert comma after each letter(unlimited char) in a cell using excel formula, without using macro.

For Example,
Actual text : EOAACADA
I need output : E,O,A,A,C,A,D,A

解决方案

If the not using a macro is absolutely essential then you're going to have fun ...

You can use the SUBSTITUTE worksheet function to replace "A" with "A," etc, but the function very quickly gets very long...

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"A","A,"),"B","B,"),"C","C,"),"D","D,"),"E","E,"),"F","F,"),"G","G,"),"H","H,")

is about as far as you can get before Excel 2002 throws an error.

So you are going to have to have hidden cells to do the interim substitutions.

I set up a worksheet with the following text in Cell A4

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789

Then placed the following formulae into cells B4 through to I4

= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"A","A,"),"B","B,"),"C","C,"),"D","D,"),"E","E,"),"F","F,"),"G","G,"),"H","H,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"I","I,"),"J","J,"),"K","K,"),"L","L,"),"M","M,"),"N","N,"),"O","O,"),"P","P,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C4,"Q","Q,"),"R","R,"),"S","S,"),"T","T,"),"U","U,"),"V","V,"),"W","W,"),"X","X,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4,"i","i,"),"j","j,"),"k","k,"),"l","l,"),"m","m,"),"n","n,"),"o","o,"),"p","p,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E4,"a","a,"),"b","b,"),"c","c,"),"d","d,"),"e","e,"),"f","f,"),"g","g,"),"h","h,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F4,"q","q,"),"r","r,"),"s","s,"),"t","t,"),"u","u,"),"v","v,"),"w","w,"),"x","x,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G4,"Y","Y,"),"Z","Z,"),"y","y,"),"z","z,"),"0","0,"),"1","1,"),"2","2,"),"3","3,")
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G4,"4","4,"),"5","5,"),"6","6,"),"7","7,"),"8","8,"),"9","9,")

The answer you need is in column I, and you will need to hide columns B through H inclusive

As you can see this is truly awful.

Another option is to write an Add-In - this link will give you a starting point http://www.pcmag.com/article2/0,2817,715709,00.asp[^] and you could just use a function similar to

Function AddCommas(s As String) As String

    Dim i As Integer
    Dim sNew As String
    sNew = ""
    For i = 1 To Len(s)
        sNew = sNew + Mid(s, i, 1) + ","
    Next
    AddCommas = sNew
End Function

why not just use the search and replace function. Easy, thats what I did,
search for ALL or the item if its AABBCCDD then replace with A,A,B,B,C,C,D,D

no programming needed.

这篇关于如何在excel公式中的每个字母后插入逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！