CODEWITHSUNDEEP
stringexcelvba
kgk
kgk

Reputation: 659

Put symbols in between a string in Excel

I have a columns of strings as follows. How can I put the symbol '<' in between the characters ?

 'ABCDE'
 'BCG'
 'ABCD'

The expected output should be:

  A<B<C<D<E
  B<C<G
  A<B<C<D

Upvotes: 0

Views: 1047

Answers (5)

Slai
Slai

Reputation: 22866

It can probably be done with Excel formula for any length, but here is the shortest VBA solution

For Each c In Range("A:A").SpecialCells(xlCellTypeConstants)
    c.Value2 = Replace( Left$( StrConv( c, vbUnicode), Len(c) * 2 - 1), vbNullChar, "<")
Next

Upvotes: 0

Alex P
Alex P

Reputation: 12489

This worked for me:

Sub SymbolInsert()
    Dim cl As Range, temp As String

    For Each cl In Range("A1:A3") '~~~> Define your range here

        For i = 1 To Len(cl)
            temp = temp & Mid(cl, i, 1) & "<"
        Next i

        cl = IIf(VBA.Right$(temp, 1) = "<", VBA.Left$(temp, Len(temp) - 1), temp)
        temp = vbNullString

    Next cl
End Sub

Upvotes: 0

user6698332
user6698332

Reputation: 427

Such things are not for formulas...

As you tag question as Excel-VBA too, so:

'''''''
Private Sub sb_Test_fp_AddSym()
    Debug.Print fp_AddSym("abncd", "<")
End Sub

Public Function fp_AddSym(pStr$, pSym$) As String
Dim i&, j&, iLB&, iUBs&, iUBt&
Dim tSrc() As Byte, tTgt() As Byte, tSym As Byte

    tSrc = pStr
    tSym = Asc(pSym)

    iLB = LBound(tSrc)
    iUBs = UBound(tSrc)
    iUBt = iUBs * 2 + 3
    ReDim tTgt(iLB To iUBt)

    For i = iLB To iUBs Step 2
        j = i * 2
        tTgt(j) = tSrc(i)
        tTgt(j + 1) = tSrc(i + 1)
        tTgt(j + 2) = tSym
        tTgt(j + 3) = 0
    Next

    ReDim Preserve tTgt(iLB To (iUBt - 4))

    Debug.Print tTgt
    Stop
    fp_AddSym = tTgt
End Function
'''

Upvotes: 0

teylyn
teylyn

Reputation: 35900

A manual, one-off, no-VBA approach would be:

  • use the Text to Columns tool with Fixed Width and place the markers after each character.
  • then use a formula like this to append values and separator

The formula could look like this if your values are in row 1

=A1&IF(LEN(B1)>0,">"&B1,"")&IF(LEN(C1)>0,">"&C1,"")&IF(LEN(D1)>0,">"&D1,"")&IF(LEN(E1)>0,">"&E1,"")

Adjust formula to suit the maximum number of characters in a cell.

Upvotes: 0

Daniel Lee
Daniel Lee

Reputation: 8001

=concatenate(left(A1,1),"<",mid(A1,2,1),"<",mid(A1,3,1),(if(len(A1)>3,"<"&mid(A1,4,1)&if(len(A1)>4,"<"&mid(A1,5,1),""),"")))

Will do what you want for values up to 5 letters, and as few as 3 letters. Otherwise you can change it.

Basically it adds a "<" between the first 3 letters and then checks whether the string is longer than 3 letters and if so, adds more "<" characters. If this needs to be more dynamic it's far easier in vba.

Upvotes: 1

Related Questions