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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
=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.
=REPLACE(A1,FIND("123abcd"
This will replace the first character after the desired chain of characters using his formula with find.
Actually, +7
ASKER
Thank you - you have no idea how much time this will save :-)
=LEFT(A1,FIND("123abcd",A1