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.

 

[Ron Rosenfeld]

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.

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))

 

[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

 

[Ron Rosenfeld]

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

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")))

 

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

 

[Ron Rosenfeld]

Hi Ron

OK thanks for your time and expertise.

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

Many thanks.

Glad to help. Thanks for the feedback.

 

[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