How to remove all characters after 17th in cell

[Radoman]

Hello

I have several rows of text data, the first 17 characters of which are in this format (4 letters_-_DD.MM.YYYY

It's then followed by a variable number of characters i.e.

ABCD - 01.02.2003 ABCDEFG HIJ KLMNO

I would be grateful for a formula that will delete all characters to the right of the 17th character (i.e. the '3').

Many thanks!

 

[AlphaFrog]

If your text is in cell A1, put this in B1...

=LEFT(A1,17)

Copy this down column B

If you want to then delete column A because it's not needed any more

Select Column B and copy it
Select from the menu Edit\ Paste Special: Values

This will replace the formulas in column B with their results. You could now delete column A

 

[meldoc]

Hello

Try.

=REPLACE(A1,18,50,"")

 

[Radoman]

That's fantastic, guys, many thanks, works a treat.

Can I just ask you one more thing please?

How can I identify and remove the last 4 characters from any cell containing file extensions .jpg .txt or .rtf? Not all cells contain these characters!

Thanks once again.

 

[meldoc]

Hello

Try.

=SUBSTITUTE(A2,LOOKUP(RIGHT(A2,4),{".jpg",".rtf",".txt"}),"")

 

[barry houdini]

Perhaps use this formula

=LEFT(A2,LEN(A2)-IF(OR(RIGHT(A2,4)={".jpg",".rtf",".txt"}),4,0))

 

[Radoman]

Meldoc/Barry - many thanks to you both, they both work fine.