Excel Forumla
chris_elevate
Posts: 585 Forum Member
✭✭
Hi,
I wondered if there was a way that I could take the number used in a cell to help form an IF statement.
For example I have a row of cells:
Level 1 Level 2 Level 3 Level 4 Level 5
Could I write a statement that will use the number 1, 2, 3, 4, or 5 from above rather than using the word "Level 1" etc.
Is this possible?
Thanks
I wondered if there was a way that I could take the number used in a cell to help form an IF statement.
For example I have a row of cells:
Level 1 Level 2 Level 3 Level 4 Level 5
Could I write a statement that will use the number 1, 2, 3, 4, or 5 from above rather than using the word "Level 1" etc.
Is this possible?
Thanks
0
Comments
=VALUE(TRIM(RIGHT(A1,2)))
where A1 is the cell containing your "Level 1" text amd the "2" at the end of the formula is the number of numeric digits your levels might contain. So if you have up to 99 levels then keep it at 2, if you have 100-999 levels then change it to 3 but if you've no more than 9 levels then change it to 1. Then copy the formula to B2, B3, etc.
But I'm sure someone will offer a better solution.
=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
That would only work if the values were Level01, Level02, etc. Though for the '2' you could substitute the Excel equivalent of (LEN(A2)-5).