Excel extract substring from string
This problem can be broken down into two steps:
- Find the index in the string of your desired split character (in this case,
"-"
or" - "
). - Get the prefix substring from the beginning of the original text to the split index.
The FIND
and SEARCH
commands each would return the index of a given needle
in a haystack
(FIND
is case-sensitive, SEARCH
is case-insensitive and allows wildcards). Given that, we have:
FIND(search_text, source_cell, start_index)
or in this case:
FIND(" - ", A1, 1)
Once we have the index, we need the prefix of source_cell
to do the "split". MID
does just that:
MID(source_cell, start_index, num_characters)
Putting them both together, we have:
=MID(A1,1,FIND(" - ",A1,1))
with A1 having text of ABC - DEF
gives ABC
.
Expanding upon Andrew's answer based on your edit:
to find the character string to split at, we are using the FIND
function.
If the FIND
fails to locate the string given, it returns a #VALUE?
error. So we will need to check for this value and use a substitute value instead.
To check for any error value including #VALUE
, we use the ISERROR
function, thus:
=ISERROR(FIND(" - ", A1, 1))
that will be true if the FIND
function can't find the " - " string in the A1 cell. So we use that to decide which value to use:
=IF(ISERROR(FIND(" - ", A1, 1)), A1, MID(A1, 1, FIND(" - ", A1, 1)))
That says that if the find command returns an error, use the unmodified A1 cell. Otherwise, do the MID
function that Andrew already provided.
Thank you @AndrewColeson for your answer.
So just to add to that, if you want everything on the right hand side of the -
, use this code:
=MID(A1,LEN(B1)+3,LEN(A1))
Which is:
A1 = ABC - DEF
B1 = =MID(A1,1,FIND(" - ",A1,1))
B1 = ABC
Therefore A1 = DEF
This code is great for if you have an undefined number of characters after the -
.
For instance:
If you have:
ABC - DEFG
AB - CDEFGH
...