No problem, just a long chunk
=IF(RIGHT(A1,1)="1",LEFT(A1,LEN(A1)-1)&"c",IF(RIGHT(A1,1)="5",LEFT(A1,LEN(A1)-1)&"b",IF(RIGHT(A1,1)="9",LEFT(A1,LEN(A1)-1)&"a","")))

This makes it 3.a

=IF(RIGHT(A1,1)="1",LEFT(A1,LEN(A1)-2)&"c",IF(RIGHT(A1,1)="5",LEFT(A1,LEN(A1)-2)&"b",IF(RIGHT(A1,1)="9",LEFT(A1,LEN(A1)-2)&"a","")))

This is 3a
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

Many thanks, that has worked but what I now need is for the decimal place to
be replaced by the letter eg 5.5 becomes 5b

"John Bundy" wrote:

Assuming the data is in column A
=IF(RIGHT(A1,1)="1",A1&"c",IF(RIGHT(A1,1)="5",A1&" b",IF(RIGHT(A1,1)="9",A1&"a","")))

Now this only handles the .1, .5, and .9 as specified
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given 3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data