CODEWITHSUNDEEP
excelexcel-2007
DannyBland
DannyBland

Reputation: 493

How to ignore the last two words in a cell

I need a formula that will return the County, separate from the code and date.

Kent BEC100 30/09/14

Not all counties are one word so I need a formula that ignores the last two words, I have one that will find the last word, second last word, first word, but I need one that will pull everything except the last two words. There are occasions where the county isn't present so I'm thinking I need to add an ISERROR in?

Bedford BED101 30/09/14 
BLA102 30/09/14
Lancs BOL100 30/09/14
Coventry, West Midla COV100 30/09/14
west Sussex CRA101 30/09/14

Upvotes: 0

Views: 279

Answers (2)

barry houdini
barry houdini

Reputation: 46331

In your examples code and date are always the same length so this formula will work if that always holds

=TRIM(MID(A1,1,LEN(A1)-15))

Upvotes: 2

Bathsheba
Bathsheba

Reputation: 234655

If your string does not contain a pipe and is already trimmed, then use

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",-1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

Here's how it works:

LEN(A1)-LEN(SUBSTITUTE(A1," ","") is the number of spaces.

SUBSTITUTE inserts a pipe at the penultimate space (with the help of my -1)

FIND gets the position of that pipe.

LEFT extracts the string up to that point.

If your string does contain a pipe, then use a different character. You can enclose the whole thing inside an IFERROR (you allude to this in the question) if you need more robustness.

Upvotes: 2

Related Questions