excel - Replace values between range to single value -
i have column numerical data in cells ranging 1 500. want replace numbers 1,2,3,4,5,6,7,8,9 string "1-9". meaning if cell has 1 replace "1-9". if cell has 22 replace "20-29" , on.
what formula this?
please help.
here crude way:
=if(len(a1)=1,"1-9",if(len(a1)=2,left(a1,1)&"0-"&left(a1,1)&"9",if(len(a1)=3,left(a1,2)&"0-"&left(a1,2)&"9")))
examples
1 1-9 22 20-29 488 480-489
assumes values listed in column a.
Comments
Post a Comment