String length variable in length but delete last 4 chars

[matthewjapp]

Hi there,

I know my left/right/len etc

But...

I have arange of values in A ranging from 5 to 100 chars

How do I delete the last 4 chars of each cell (these chars appear in the string in various points so cannot just do a ctrl + h..

Answers greatly appreciated!

Matt

 

[JackDanIce]

I don't think you've been specific enough in what you're asking, as intuitively, I'd think you'd just need:

=LEFT(A1,LEN(A1)-4))

But you say you know about the LEFT and LEN functions.

Can you post an example of your data or be more precise in where the problem lies?

Unless you have values less than 4 characters long, in which case, try:

IF(LEN(A1)>=4,LEFT(A1,LEN(A1)-4)),A1)

 

[njimack]

Excel Workbook
	A	B
1	abcde	a
2	abcdef	ab
3	abcdefg	abc
4	abcdefgh	abcd
5	abcdefghi	abcde
6	abcdefghij	abcdef
7	abcdefghijl	abcdefg
Sheet3

 

[matthewjapp]

sorry guys, cracked it, did a len on A, then did left A len A-4..

thanks

 

[matthewjapp]

thanks, but figured that and have had my moment of personal disgust at having to post here for that one ha ha ta!

 

[JackDanIce]

As long as you're learning.. it's all good!

 

[matthewjapp]

Very true - like your quote by the way )