Adding string after last '.' in cell in Excel

Question

I have a list of emails (roughly 2000) in excel. I need to add a string, "string" to the end of the domain, before the .com.

Before: [email protected]
After: email@yahoo.**string**.com

Before: [email protected]
After: [email protected].**string**.com

I tried to use concatenate but that doesnt seem correct...

Any help would be greatly appreciated!

BruceWayne · Answer 1 · 2017-01-09T18:40:06.270

1

If it always ends in .com and you want to insert some text, you can do this (assuming your original strings start in A1) in B1. Let's say your custom string is stored in C1.

=Left(A1,Len(A1)-4)&$C$1&".com"

edit: if not always .com, =SUBSTITUTE(A1,".","." & $C$1 & ".",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))) (thanks @ScottCraner)

edited Jan 09 '17 at 18:40

answered Jan 09 '17 at 18:23

BruceWayne

22,923
15
65
110

1

Just replace the `.` with `"." & $C$1 & "."` : `=SUBSTITUTE(A1,".","." & $C$1 & ".")` – Scott Craner Jan 09 '17 at 18:28
@ScottCraner - Do you want to make that an answer? I was going to do more, but the OP just mentions `.com`. If not, I'll edit it in to my answer, but don't want to steal your mighty thunder! – BruceWayne Jan 09 '17 at 18:34
Have at it, I do not have the time currently to post an answer. – Scott Craner Jan 09 '17 at 18:35
You cant replace the dots because of the `[email protected]` example. It would return `email@school.*string*.edu.*string*.com` – Moacir Jan 09 '17 at 18:37
2

@Moacir Then it would be: `=SUBSTITUTE(A1,".","." & $C$1 & ".",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))` and it will change the last only. – Scott Craner Jan 09 '17 at 18:39

score 1 · Answer 2 · answered Jan 09 '17 at 18:36

Column A the origin email

Column B will be where you put the formula

Column C the string (Or if its the same string for all of them just replace C1 with it)

=LEFT(A1,LEN(A1-4))&C1&RIGHT(A1,4)

Remember this will only work if the last characters are always 4 (Like .com, .net and not .br, .au)

score 1 · Answer 3 · edited May 23 '17 at 10:30

Say we have text in A1 with at least one period in the text and zero or more characters after the last period. We know that the position of the last period is given by:

=FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),(LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

So we put this in B1 and in C1 enter:

=LEFT(A1,B1-1) & "whatever" & MID(A1,B1,9999)

This approach does not depend on the length of the string following that last period.

Adding string after last '.' in cell in Excel

3 Answers3