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 have one column created with 5 or 6 digits in the cells with a plus sign two from the last digit (ex. 000+00; 0000+00). I need to create two other columns, one with what's on the left side of the plus sign, and one with what's on it's right side. I wouldn't mind doing it by hand, but there's 103,711 entries... anyone have any ideas?!

share|improve this question

migrated from Oct 19 '11 at 16:47

This question came from our site for professional and enthusiast programmers.

Assumptions mentioned in the OP:

  1. There will always be either 3 or 4 digits before the plus sign
  2. There will always be a plus sign
  3. There will always be 2 digits after the plus sign

If A1 is the cell with the full text, in the cells for left side:

=if(len(A1)=6, left(A1,3), left(A1,4))

In those for the right side:


Here's a good function reference:

share|improve this answer
wow!!! thank you so much for your help! – CriStyle Oct 18 '11 at 18:20

Here's another solution. These formulas obtain the position of the "+" in each string and then use that number to obtain the left and right substrings (regardless of their length).

Left Side:


Right Side:


Or if each of the entries only have 2 characters to the right of the "+", Jonathan's formula would be better: =RIGHT(A1,2)

share|improve this answer

You must log in to answer this question.