10 digit numbers in cell To be replaced

[Amitlo26]

Dear Masters,
Greetings,

Query is as follows I have number like this in cell 8700512345.it will be always 10 digits.
I want to replace 5 number by new number 6 for example 8700612345 .
Problem is that it should be replaced only if it's true that 5number is 5 and if it's other number no substitute to be done.
I am able to this by breaking the number right and left and then in midddle 0 and the joining all.
I have used substitute formula but it fails when 5 number is other than 5.
Thanks and regards
Amit

[LeoTaxi]

With number in A1

In cell you want result

Please Login or Register  to view this content.

Kind regards
Leo

[Amitlo26]

Thanks
I tried it but it went into error

[AliGW]

Have you tried changing semi-colons to commas?

=IF(MID(A1,5,1)="5",LEFT(A1,4)&6&RIGHT(A1,5),A1)

[shukla.ankur281190]

Assuming your data iin A1

B1=IF(MID(A1,5,1)="5",REPLACE(A1,4,1,6),A1)

P.s-Replace function does not change numbers into text.
Hope it will help you.

[FDibbins]

Another version...
=A1+if(mid(A1,5,1)=5,100000,0)

[shukla.ankur281190]

Another version...
=A1+if(mid(A1,5,1)=5,100000,0)

Mr. Ford

=A1+if(mid(A1,5,1)="5",100000,0)

Double quotes was missing in logical test but This is beautiful trick

[FDibbins]

oops yes, thanks, I did leave out the ""

[Winon]

Since the requirement is also;

Problem is that it should be replaced only if it's true that 5number is 5 and if it's other number no substitute to be done.

I would suggest that the formula to be amended to :

Please Login or Register  to view this content.

to cater for the display of "Blanks" if 5<>5

or

Please Login or Register  to view this content.

to avoid the display of "Blanks" if 5<>5 or "0" if Range Row A is Empty.

Cheers guys!

[Amitlo26]

Thanks for the feedback

[FDibbins]

Thanks for the feedback