You could use the combination of =LEFT()
and =SEARCH()
.
SEARCH
gives you the characters position in a string and LEFT
returns every character on the left of the position you want.
For example, for the Cell content in A1 = VATICAN - The Pope...
=SEARCH("-",A1)
will give you 6
=LEFT(A1,6)
will give you VATICAN -
and the combination
=LEFT(A1,SEARCH("-",A1))
will return VATICAN -
The same works for any other character as i.e. ;
or :
Note that the LEFT()
function will get you also the searched character. You can append a -1
after the SEARCH()
function to avoid it.
If the character is not found, you will get an error, this is how you overcome it:
=IFERROR(RETURN,RETURN_IF_ERROR)
If you want to write for example (unknown) you should use
=IFERROR(LEFT(A1,SEARCH("-",A1)-1),"unknown")
but you can also leave it blank by puttnig only ""
.
Concatenation:
You can concatenate this formulas several times to get different characters. Keep in mind that Excel will work the formula ltr.
If you want to search for "-"
and, if not found, for ":"
you would use:
=IFERROR(LEFT(A1,SEARCH("-",A1)-1),IFERROR(LEFT(A1,SEARCH(":",A1)-1),"unknown"))
EDIT:
If you want to look only in the first, let's say, 20 characters, just change SEARCH("-",A1)
for SEARCH("-",LEFT(A1,20))