I am trying to trim some data that is all in the same column, however not all data elements are going to be the same length, for instance, if a cell contains NUSGCS I want to trim it to 12 characters, however if it contains CTCS it would be 11, or contains P- it would be 6 and other variations on this theme. Is there anyway to do this in a formula? I have tried using an "if Contains" stacked with a "Trim Left" function but I keep getting a malformed statement error. Attached is some sample data containing what I am working with.
if Contains([SECONDARY REFERENCE NUMBER], 'NUSCGS') then TrimLeft([SECONDARY REFERENCE NUMBER], '12') else
if Contains([SECONDARY REFERENCE NUMBER], 'CTCS') then TrimLeft([SECONDARY REFERENCE NUMBER], '11') else 'Blank' endif
