Stripping out selected sections from existing cell contents

[Colin Hayes]

Hi All

I use this code to strip out everything after the underscore from my
cell contents :

=TRIM(LEFT(A2,FIND("_",A2)-1))

Some (but not all) of my cells also start with the letter 'C'.

For the cells which start this way , I would also like the 'C' at the
beginning to be stripped out.

Is this possible by extending my code above?


Grateful for any advice.

 

[Don Guillett Excel MVP]

Hi All

I use this code to strip out everything after the underscore from my
cell contents :

=TRIM(LEFT(A2,FIND("_",A2)-1))

Some (but not all) of my cells also start with the letter 'C'.

For the cells which start this way , I would also like the 'C' at the
beginning to be stripped out.

Is this possible by extending my code above?

Grateful for any advice.

One way
=TRIM(LEFT(IF(LEFT(A12)="c",RIGHT(A12,LEN(A12)-
FIND("c",A12)),A12),FIND("_",IF(LEFT(A12)="c",RIGHT(A12,LEN(A12)-
FIND("c",A12)),A12))-1))