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
1		249		201	230
2	123ABC45	123-ABC-45		123-ABC-45	123-ABC-45
3	2155G100	2155-G-100		2155-G-100	2155-G-100
4	1DSTH67	1-DSTH-67		1-DSTH-67	1-DSTH-67

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula B1 =LEN(FORMULATEXT(B2)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B2 {=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,"-")} D2 {=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))} E2 {=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))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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]

Column1	Custom
123ABC45	123-ABC-45
2155G100	2155-G-100
1DSTH67	1-DSTH-67
3256ACD569	3256-ACD-569
369DG699	369-DG-699
2569AVBT789	2569-AVBT-789
125abg789	125-abg-789
abc789cfg	abc-789-cfg
256abc369	256-abc-369

<colgroup><col><col></colgroup><tbody>
</tbody>

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"