replacing a number to the next number in the middle of a cell

[rfletcher35]

Guys,

I have a number of email addresses in column b on my sheet (Sample below)

LTablet181@blahblahblah.co.uk
LTablet182@blahblahblah.co.uk
LTablet183@blahblahblah.co.uk

This goes on quite a bit; I'm wanting a solution that will allow me to replace the number in the middle in this sample 181 to the next. If I drag down from 181 it simply has 181 in the bow whereas if I do drag down, I need a formula where this increments by one in some way so the next will be 182, 183, 184 etc. Hope this makes sense.

Thanks

Fletch

 

[Deek6t8]

so you could have something like ="LTablet"&value(mid(A2,8,3))+1&"@blahblahblah.co.uk" (assuming A2 is the cell above and the number is always in exactly the same place

 

[rfletcher35]

so you could have something like ="LTablet"&value(mid(A2,8,3))+1&"@blahblahblah.co.uk" (assuming A2 is the cell above and the number is always in exactly the same place

Had a look at your formula, it's column B i'm copying down on so change the A to B but just got a value error

 

[sparky2205]

In B2:

=LEFT(B1,7)&MID(B1,8,3)+1&RIGHT(B1,LEN(B1)-FIND("@",B1)+1)

 

[Deek6t8]

Seemed to work okay for me

 

[rfletcher35]

In B2:

=LEFT(B1,7)&MID(B1,8,3)+1&RIGHT(B1,LEN(B1)-FIND("@",B1)+1)

That's great got those to work thank you