Link to home
Start Free TrialLog in
Avatar of agwalsh
agwalsh

asked on

Removing last character from 7 digit serial number and replacing with zero

Hi Folks
I suspect this solution is some combination of xRight and Replace functions...but essentiall y..how do I (via formula) remove the last digit of a 7 character combination (which could be any number or text) and replace with a zero?
Thanks..
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

If I understand correctly, you will have text like: xxxxxxx123abcdYxxx and you want to replace Y with 0.
=LEFT(A1,FIND("123abcd",A1)+6) & "0" & MID(A1,FIND("123abcd",A1)+8,LEN(A1))
ASKER CERTIFIED SOLUTION
Avatar of TigerMan
TigerMan
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of barry houdini
You can use REPLACE function to replace a specific amount of characters from a specific position with other text, so to replace charcater 7 with zero

=REPLACE(A1,7,1,0)

regards, barry
Expanding on Barry's idea:
=REPLACE(A1,FIND("123abcd",A1)+6,1,0)
This will replace the first character after the desired chain of characters using his formula with find.
Actually, +7
Avatar of agwalsh
agwalsh

ASKER

Thank you - you have no idea how much time this will save :-)