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

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -

php - Accessing static methods using newly created $obj or using class Name -