Stripping selected text out from existing content

[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.

 

[Max]

Think you could try this logic:
=if(left(A2)="C",mid("yourformula",2,99),"yourformula")

 

[Colin Hayes]

Think you could try this logic:
=if(left(A2)="C",mid("yourformula",2,99),"yourformula")

Hi Max

Thanks for getting back.

For 'your formula' I assume you mean to insert my underscore character.

I did try it , but it just blanks out the entire cell where it starts
with 'C'. I only really need the C to be removed.

Here's an example of what I mean :

Cell A2 contains

C106552_7bx95

This formula

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

makes it

C106552

How can I modify the formula so that the 'C' is removed too?


Results which don't begin with 'C' would be unaffected.


Thanks again.


Best Wishes.

 

[Colin Hayes]

If the TRIM's are really necessary, in other words, if there might be leading and/or
trailing spaces in
the cell, then:

=IF(A2="","",MID(TRIM(A2),1+(LEFT(TRIM(A2),1)="C"),FIND("_",TRIM(A2))-1))

If there are no leading/trailing spaces in the cell, then:

=IF(A2="","",MID(A2,1+(LEFT(A2,1)="C"),FIND("_",A2)-1))

Hi Ron

Thanks for getting back.

That fixed it - or nearly..

Where I have this content :

106366_7bx95
106319_7bx95
106208_12bx41
106239_12bx29
106241_12bx29
106258_12bx29

You formula correctly gives

106366
106319
106208
106239
106241
106258


Where I have

C106271_12bx29
C106186_12bx29
C106285_12bx29
C106268_12bx29
C106231_12bx29

Your formula leaves it with a trailing underscore :

106271_
106186_
106285_
106268_
106231_


Is it possible to tweak it so that those starting with C no longer have
the trailing underscore showing?

(I did try changing the final -1 in the formula to -2. This did remove
the final underscore for those beginning with C, but also removed the
final number for those not starting with C , which of renders it
useless.)

Thanks again Ron

 

[Colin Hayes]

Sorry about that. I overlooked the trailing underscore when testing.

Try:

=IF(A1="","",MID(A1,1+(LEFT(A1,1)="C"),FIND("_",A1)-1-(LEFT(A1,1)="C")))

or, if you need the TRIM:

=IF(TRIM(A1)="","",MID(TRIM(A1),1+(LEFT(TRIM(A1),1)="C"),FIND("_",TRIM(A1))-1
-(LEFT(TRIM(A1),1)="C")))

Hi Ron

OK thanks for your time and expertise.

This is working perfectly. Exactly correct solution to the problem.

Many thanks.

 

[Don Guillett Excel MVP]

Sorry about that.  I overlooked the trailing underscore when testing.

Try:

=IF(A1="","",MID(A1,1+(LEFT(A1,1)="C"),FIND("_",A1)-1-(LEFT(A1,1)="C")))

or, if you need the TRIM:

=IF(TRIM(A1)="","",MID(TRIM(A1),1+(LEFT(TRIM(A1),1)="C"),FIND("_",TRIM(A1))­-1-(LEFT(TRIM(A1),1)="C")))- Hide quoted text -

- Show quoted text -

To trim simply add trim( formula )
=IF(A14="","",TRIM(MID(A14,1+(LEFT(A14,1)="C"),FIND("_",A14)-1-
(LEFT(A14,1)="C"))))

 

[Max]

For 'your formula' I assume you mean to insert my underscore character.

No, I meant your entire formula as you posted.
The MID would be a way to "strip" out the "C"

Sorry for not making it clear earlier.
Glad to see you got an answer that worked ok for you