Insert Dash Between Numbers and Text

[mgirvin]

Dear Team,

I would like to Insert Dashes Between Numbers and Text, like this:

Start Code Desired Result
123ABC45 123-ABC-45
2155G100 2155-G-100
1DSTH67 1-DSTH-67



I have this ridiculous formula:

=REPLACE(REPLACE(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0),0,"-"),MATCH(2^15,1/ISERROR(--MID(REPLACE(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0),0,"-"),ROW(INDIRECT("1:"&(LEN(A2)+1))),1)))+1,0,"-")

Anyone have a more efficient Excel Spreadsheet Formula or Power Query Method?

 

[Scott Huish]

Here's a UDF way:

Function LetterDash(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "([0-9])([A-Z]+)([0-9])"
    LetterDash = .Replace(r, "$1-$2-$3")
End With
End Function

Excel 2010
	A	B
1	123ABC45	123-ABC-45
2	2155G100	2155-G-100
3	1DSTH67	1-DSTH-67
Sheet1

Cell Formulas
Range	Formula
B1	=letterdash(A1)
B2	=letterdash(A2)
B3	=letterdash(A3)

 

[Fennek]

Hello,

first: Thank you for "ExcelsFun", great videos.

This UDF should help

function iDash(rng as range) as string
Tx = rng.value
TT = left(Tx,1)
if isnumeric(TT) then
    Ty = "N"
else
    Ty = "C"
endif
for k = 2 to len(Tx)
    if isnumeric(mid(Tx,k,1)) then
        if Ty = "C" then 
            TT = TT  & "-"
            Ty = "N"
        endif
    else
        if Ty = "N" then
            TT = TT & "-"
            Ty = "C"
        endif
    endif
    TT = TT  & mid(Tx,k,1)
next k
iDash = TT
End function

regards

 

[Fennek]

Hello,

for the case "Numbers - Letters -Numbers" this could work:

Function iTest(rng as range) as string
    TT = val(rng.value) & "-"
    for k = len(TT) to len(rng.value)
        if mid(rng.value,k,1) like "#" then exit for
    next k
    TT = TT & mid(rng.value, len(TT),k - len(TT)) & "-"
    TT = TT & right(rng.value, len(rng.value) -k+1)
    iTest = TT
end function

regards

(sorry, badly tested)

 

[Eric W]

I'm not sure what your criteria for "ridiculous" is. You have some VBA options to consider. If you want to stick with a formula, and you have a newer version (2016+) of Excel that has CONCAT, and you think "shorter = less ridiculous" then maybe:

	A	B	C	D	E
123ABC45	123-ABC-45	123-ABC-45	123-ABC-45
2155G100	2155-G-100	2155-G-100	2155-G-100
1DSTH67	1-DSTH-67	1-DSTH-67	1-DSTH-67

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]249[/TD]
[TD="align: right"][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]230[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=LEN(FORMULATEXT(B2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=REPLACE(REPLACE(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0),0,"-"),MATCH(2^15,1/ISERROR(--MID(REPLACE(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0),0,"-"),ROW(INDIRECT("1:"&(LEN(A2)+1))),1)))+1,0,"-")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=MAX(IFERROR(MID(A2,1,ROW(INDIRECT("1:99")))+0,0))&"-"&CONCAT(IF(ISERROR(MID(A2,ROW(INDIRECT("1:99")),1)+0),MID(A2,ROW(INDIRECT("1:99")),1),""))&"-"&MAX(IFERROR(MID(A2,ROW(INDIRECT("1:99")),99)+0,0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=MAX(IFERROR(MID(A2,1,ROW(INDIRECT("1:"&LEN(A2))))+0,0))&"-"&CONCAT(IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))&"-"&MAX(IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),LEN(A2))+0,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



B2 is your formula, D2 is the CONCAT version with a maximum field length of 99, E2 uses the length of the field. Both D2 and E2 are shorter than your original formula, and IMHO a bit easier to understand. They both require the field to be number/text/number.

Good luck!

 

[István Hirsch]

Try this:

Excel Workbook
	A	B	C	D	E
1
2	123ABC45	123	ABC	45	123-ABC-45
3	2155G100	2155	G	100	2155-G-100
4	1DSTH67	1	DSTH	67	1-DSTH-67
Sheet1

 

[István Hirsch]

Try this:

Excel Workbook
	A	B
2	123ABC45	123-ABC-45
3	2155G100	2155-G-100
4	1DSTH67	1-DSTH-67
5	876458CVBNMT234	876458-CVBNMT-234
Sheet2

 

[Fennek]

Hello,

it is clear, that you excluded VBA and an easy solution is flash-fill, but it is a nice game.

To change all Number-letters-Number strings in column A:

Sub Dash_Str()
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Tx = Cells(r, 1)
    For i = Len(Tx) - 1 To 1 Step -1
        If Mid(Tx, i, 2) Like "#[A-z]" Or Mid(Tx, i, 2) Like "[A-z]#" Then Cells(r, 1).Characters(i + 1, 0).Insert ("-")
    Next i
Next r
End Sub

regards

 

[Janardhan]

[TABLE="width: 439"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Custom[/TD]
[/TR]
[TR]
[TD]123ABC45[/TD]
[TD]123-ABC-45[/TD]
[/TR]
[TR]
[TD]2155G100[/TD]
[TD]2155-G-100[/TD]
[/TR]
[TR]
[TD]1DSTH67[/TD]
[TD]1-DSTH-67[/TD]
[/TR]
[TR]
[TD]3256ACD569[/TD]
[TD]3256-ACD-569[/TD]
[/TR]
[TR]
[TD]369DG699[/TD]
[TD]369-DG-699[/TD]
[/TR]
[TR]
[TD]2569AVBT789[/TD]
[TD]2569-AVBT-789[/TD]
[/TR]
[TR]
[TD]125abg789[/TD]
[TD]125-abg-789[/TD]
[/TR]
[TR]
[TD]abc789cfg[/TD]
[TD]abc-789-cfg[/TD]
[/TR]
[TR]
[TD]256abc369[/TD]
[TD]256-abc-369[/TD]
[/TR]
</tbody>[/TABLE]

Power query code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.ToList([Column1])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "Custom", "Custom - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Custom", type text}, {"Custom - Copy", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Custom - Copy", null}}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if [#"Custom - Copy"] = null then [Custom] else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column",null,"-",Replacer.ReplaceValue,{"Custom - Copy", "Custom.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom - Copy", "No"}, {"Custom.1", "Letter"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Column1"}, {{"Count", each _, type table}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "No", each Table.Column([Count],"No")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"No", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Added Custom2" = Table.AddColumn(#"Extracted Values", "Letter", each Table.Column([Count],"Letter")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom2", {"Letter", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "No", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"No.1", "No.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"No.1", Int64.Type}, {"No.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Letter", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Letter.1", "Letter.2"}),
#"Replaced Value1" = Table.ReplaceValue(#"Split Column by Delimiter1","-","",Replacer.ReplaceText,{"No.2", "Letter.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"No.1", type text}, {"No.2", type text}, {"Letter.1", type text}, {"Letter.2", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type2", "Custom", each if[No.1]=null then[Letter.1]&"-"&[No.2]&"-"&[Letter.2] else[No.1]&"-"&[Letter.2]&"-"&[No.2]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Count", "No.1", "No.2", "Letter.1", "Letter.2"})
in
#"Removed Columns1"