Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

4 Answers 4

up vote 6 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

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 at 1:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.