I have this formula and it works well however I need the final result need to be a number. When I in

Mary Kam

I have this formula and it works well however I need the final result to be a number. It needs to be a number because the index match does not work when I am comparing this value. I tried to add value in front of the formula and it gives me invalid value. I prefer not to to add another column to resolve this problem.

=IF(CONTAINS("/", [ORIGINAL STUDY NO.]@row), RIGHT([ORIGINAL STUDY NO.]@row, LEN([ORIGINAL STUDY NO.]@row) - FIND("/", [ORIGINAL STUDY NO.]@row)), IF(CONTAINS("/", [STUDY NUMBER]@row), RIGHT([STUDY NUMBER]@row, LEN([STUDY NUMBER]@row) - FIND("/", [STUDY NUMBER]@row)), ""))

Zachary Hall

Hello,

Try: =IF(CONTAINS("/", [ORIGINAL STUDY NO.]@row), VALUE(RIGHT([ORIGINAL STUDY NO.]@row, LEN([ORIGINAL STUDY NO.]@row) - FIND("/", [ORIGINAL STUDY NO.]@row))), IF(CONTAINS("/", [STUDY NUMBER]@row), VALUE(RIGHT([STUDY NUMBER]@row, LEN([STUDY NUMBER]@row) - FIND("/", [STUDY NUMBER]@row))), ""))

Hope this helps!