Insert text before the @ character from email address in a cell

[missch]

Hi All

I know how to insert characters x spaces from left or right, but in my column have a list of email addresses.They are all completely different and vary in length an characters before and after the @ sign.

As an example I would like to change fred@smith.com to fred-123@smith.com and john@doe.com to john-123@doe.com.
The text to insert will always be -123 and always immediately before the @ sign.

Can anyone help please? I don't fancy doing 300 manually
Thanks

 

[alansidman]

Can be done quickly using Power Query. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1.1]&"-123"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Column1.1", "Custom", "Column1.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Custom", "Column1.2"},Combiner.CombineTextByDelimiter("@", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.1"})
in
    #"Removed Columns"

 

[jasonb75]

You could replace @ with -123@ either by using crtl h.

Select the whole column first, then replace all.

 

[missch]

Brilliant, so simple, can’t believe I didn’t think of it! Thanks

 

[mohadin]

HI
If you like this code

Sub test()
    a = Application.Transpose(Cells(1, 1).Resize(Cells(Rows.Count, "a").End(xlUp).Row))
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\@"
        For i = 1 To UBound(a)
            a(i) = .Replace(a(i), "-123@")
        Next
    End With
    Cells(1, 2).Resize(UBound(a)) = a
End Sub