Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am looking for a way to extract a variable length substring from a string.

My cells will look something like:

ABC - DEF
ABCDE - DEF
ABCD - ABC

I want to split the string at the - character, so the cells will become:

ABC
ABCDE
ABCD

This should be done with a formula and not VBScript.

I am using Excel 2010

EDIT

I found that the dataset doesn't always contain the - character, meaning there should be no change.

share|improve this question
up vote 19 down vote accepted

This problem can be broken down into two steps:

  1. Find the index in the string of your desired split character (in this case, "-" or " - ").
  2. 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.

share|improve this answer
    
Please see my edit. – Pieter van Niekerk Nov 17 '11 at 14:30

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.

share|improve this answer
    
Great followup, thanks! – Andrew Coleson Nov 23 '11 at 19:48

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
...
share|improve this answer
    
this does the trick also =MID(a1,FIND("-",a1)+1,LEN(a1)) without any "extra-cellular" references ) – Mikey Mar 18 at 18:01

Here is a very simple way to extract the 5th character from the left from a text string in Excel:

Suppose the character string ABCDEFGHIJ is stored in cell A1 in an Excel Spreadsheet, then the following formula

=RIGHT(LEFT(A1,5),1)

produces the 5th character from the left in the string, namely E.

share|improve this answer
    
(Is there no MID function in Excel? I'm sure it exists in legacy Visual Basic, and it would be something like =MID(A1, 5, 1)) – Valmiky Arquissandas Aug 20 '14 at 1:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .