Add specific character

[sohamkhatri]

Hello Team,

I want a help to find a excel formula for my problem.

I have a data something like below-

ABCDEF
ABSFGHFDS
DGUVHINGYIHH
FTYJJJ
FTYBJKFGY


I have a data like this where I have some some words which have letter multiple of 3

So I want to convert this data something like this

ABC-DEF
ABS-FGH-FDS
DGU-VHI-NGY-IHH
FTY-JJJ
FTY-BJK-FGY


I want that after every third letter or character "-" will be added between them.


Please help me in it.

Thank you

 

[kweaver]

Here's one idea:

Function ReGroup(str As String)
  Dim str2 As String
 
  str2 = ""
  While Len(str) >= 3
  str2 = str2 & Left(str, 3) & "-"
  str = Right(str, Len(str) - 3)
  Wend
   ReGroup = Left(str2, Len(str2) - 1)
 
End Function

Use it in a cell like B2, where your original string is in A2: =ReGroup(A2)

 

[lrobbo314]

This was my VBA approach. I know that the OP is on XL2013, but there is a formula solution as well.

SOHA
	G	H	I
8	Original	Dynamic Array	VBA
9	ABCDEF	ABC-DEF	ABC-DEF
10	ABSFGHFDS	ABS-FGH-FDS	ABS-FGH-FDS
11	DGUVHINGYIHH	DGU-VHI-NGY-IHH	DGU-VHI-NGY-IHH
12	FTYJJJ	FTY-JJJ	FTY-JJJ
13	FTYBJKFGYZ	FTY-BJK-FGY-Z	FTY-BJK-FGY-Z
Data

Cell Formulas
Range	Formula
H9:H13	H9	=HYPHENATE(G9:G13,3)
I9:I13	I9	=htt(G9)
Dynamic array formulas.

Function HTT(s As String)
With Application.WorksheetFunction
    Dim n As Integer:   n = .RoundUp(Len(s) / 3, 0) - 1
    
    For i = 1 To n
        s = Application.WorksheetFunction.Replace(s, i * 4, 0, "-")
    Next i
End With
    
HTT = s
End Function

HYPHENATE

=LAMBDA(
    text,chunk_size,
    MAP(text,
        LAMBDA(t,
            TEXTJOIN("-",,MID(t,SEQUENCE(ROUNDUP(LEN(t)/chunk_size,0),,,chunk_size),chunk_size))
        )
    )
)

 

[kweaver]

Another more flexible function might be:

Function InsertDash(s As String, intv As Long)
    If intv < 1 Then Exit Function

    Dim i As Long, result As String

    For i = 1 To Len(s) Step intv
        On Error Resume Next
        r = r & Left(s, intv) & "-"
        s = Mid(s, intv + 1, Len(s) - intv)
    Next i

    InsertDash = Left(r, Len(r) - 1)
End Function

Used as: =InsertDash(A2,3) for every 3 characters

 

[JEC]

An option with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    out = Table.TransformColumns(Source,{{"Column1", each  
            List.Accumulate({1..Text.Length(_)/3 -1}, "", (s,c)=> 
                if s = "" then Text.Insert(_, c*3, "-") else Text.Insert(s, c*3 + (c-1), "-")), type text}})
in
    out

Book1
	A	B	C
1	Column1		Column1
2	ABCDEF		ABC-DEF
3	ABSFGHFDS		ABS-FGH-FDS
4	DGUVHINGYIHH		DGU-VHI-NGY-IHH
5	FTYJJJ		FTY-JJJ
6	FTYBJKFGY		FTY-BJK-FGY
Sheet1

 

[JEC]

And here a Regex function

Function jec(cell As String) As String
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "([A-Z]{3}(?!$))+?"
   jec = .Replace(cell, "$1-")
 End With
End Function

 

[lrobbo314]

Another recursive option.

Function XH(s As String, Optional res As String)
Dim n As Integer:   n = Len(s)
res = res & IIf(n <= 3, s, Left(s, 3) & "-")
If n > 2 Then XH Right(s, n - 3), res
XH = res
End Function