Formula to extract first listed item

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

Paul Martin

Hi all

If I have in Column A a list of countries such as:

Australia
USA/Australia
USA/UK/Germany
USA

I want in Column B a list of just the first listed country. I have a
solution which seems pretty clunky and am wondering how to improve on
it. My formula looks like this:

=IF(A1="", "", IF(ISERROR(FIND("/", A1)), A1, LEFT(A1, FIND("/",
A1)-1)))

Any suggestions are appreciated. TIA.

Paul Martin

Melbourne, Australia
 
Hi Paul

Select your range of data>Control + H to bring up Find and Replace
Find /*
Replace leave blank
Replace All
 
Hi Paul

I hadn't noticed that.
|Copy column A to column B
Do the Find/replace on column B, not column A
 
Hello,

Countries in A1,etc.
=Mid(A1,1,(Search("/",A1,1)-1))


best regards

Gabor Sebo
 
Thanks Gabor, but that formula returns an error if there's no "/". My
formula above is basically the same, but it handles that error.

Paul
 
=IF(ISNUMBER(SEARCH("/",A1,1)),MID(A1,1,(SEARCH("/",A1,1)-1)),A1)

This is a correction, the other version did not work for USA less "/".

Gabor Sebo
 
Thanks all. I think Gabor's is an improvement on my formula, using
the test for ISNUMBER rather than my ISERROR, which removes the need
to test for a value being present. I've replaced MID with LEFT
however.
 
Hi Paul

Yes, but with Biff's formula, there is no need for any ISNUMBER or
ISERROR test.
 
Back
Top