from 111 to 1-1-1

[Jos1972]

hello,
I am looking for a way to split numbers to text with dashes between the numbers (i.e. 111 should become 1-1-1). I used concatenate combined with Len to make the split, however sometimes the cell contains a 3 digit number, sometimes 4 digit, up to 7 digits.
Does anyone have experience with this problem? Is there one formula that I could use?, or a macro?


Thank
Jos

 

[Sektor]

UDF.
Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5

Function SetDashes(Str As String)

    ' Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    
    Dim re As New RegExp
    Dim s As String
    
    With re
        .Global = True
        .Pattern = "(\d)"
    End With
    
    s = re.Replace(Str, "$1-")
    
    SetDashes = Left(s, Len(s) - 1)

End Function

 

[JackDanIce]

n/a

 

[Jos1972]

hello,
Thanks for the quick reply.
it works.
cheers,
jos

 

[Comfy]

You could 4 conditional formatting rules.

Rule 1:

=IF(LEN(A1)=4,1,0)

Custom format would be #-#-#-#
Rule 2:

=IF(LEN(A1)=5,1,0)

Custom format would be #-#-#-#-#
Rule 3:

=IF(LEN(A1)=6,1,0)

Custom format would be #-#-#-#-#-#
Rule 4:

=IF(LEN(A1)=7,1,0)

Custom format would be #-#-#-#-#-#-#

/Comfy

 

[GlennUK]

Or install MOREFUNC ...

http://xcell05.free.fr/downloads/Morefunc.zip

and use MCONCAT in an array formula:

Excel Workbook
	A	B	C
1	Inputs	Outputs ( from REGEXP udf )	Outputs ( from MOREFUNC function )
2	111	1-1-1	1-1-1
3	234	2-3-4	2-3-4
4	213	2-1-3	2-1-3
5	2356	2-3-5-6	2-3-5-6
6	54256	5-4-2-5-6	5-4-2-5-6
7	765433	7-6-5-4-3-3	7-6-5-4-3-3
8	45234	4-5-2-3-4	4-5-2-3-4
9	234235	2-3-4-2-3-5	2-3-4-2-3-5
test

 

[barry houdini]

Try this formula

=TEXT(A1,0&REPT("-0",LEN(A1)-1))

 

[GlennUK]

Try this formula

=TEXT(A1,0&REPT("-0",LEN(A1)-1))

D'oh! Why didn't I think of that!