Trimming!

[jonnykanzen]

Hi,

I have just imported alot of data into excel which i was wondering if theres a way of trimming

theres a column where all the cell values end in ".GB" or ".ES" basically a "." then 2 letters. Is there a formula to trim this off?

Thanks so much

[jonnykanzen]

Sorry forgot to mention.

Not ALL the cells end like this. . . Some dont have the ".XX" at the end and obviously i wouldnt want t his trimmed

[Andy Pope]

In a spare column you could use the formula

=IF(LEFT(RIGHT(A1,3),1)=".",LEFT(A1,LEN(A1)-2),A1)

This leaves the fullstop in. If you want that removed use -3 instead of -2


Once your happen you can use copy / pastespecial / values

[jonnykanzen]

hi there thanks for your help.

thats works nicely for all the cells that end it .XX but the ones that dont it produces a 0. . anyway to make this work?

heres an eg of my cells:
AOEP
AFE.GB
AGA.GB
AGK.GB
AGAP.GB
AGRI
AHMP

thanks

[Andy Pope]

For me your test data returns

AOEP
AFE.
AGA.
AGK.
AGAP.
AGRI
AHMP

[jonnykanzen]

returns for me:
0
AFE
AGA
AGK
AGAP
0
0

[jonnykanzen]

Ok fixed it!

[Andy Pope]

what did you do to fix it, in case anybody else reading this has the same problem.