Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

How do I delete everything but the first 6 characters in a cell throughout a whole column?

The cells contain identification numbers and in some cases more identifications have been made in each row. I am ony interested in the first identification number of 6 characters, consisting of both letters and numbers.

Ex: X47551;X85H52;X14J56

So how do I remove everything but the 'X47551'?

share|improve this question
This function is what you need:…. – whatever Mar 5 '13 at 12:56

You could create another column that uses a formula to pull only the 6 left characters:

=LEFT(A1,6) where A1 = "X47551;X85H52;X14J56"

After using the formula you may want a column of the text that no longer depends on it. This could replace your original column, if desired:

  1. Select the column
  2. Right click -> Copy
  3. Select another blank column
  4. Right click -> Paste special...
  5. Select Paste Values -> Ok

Alternatively, you may want to split the existing column by the semicolon delimiter:

  1. Select the column
  2. Navigate to Data -> Text to Columns
  3. Select Delimited -> Next
  4. Select Other -> Enter ';' -> Next
  5. Change format, if desired -> Finish

This will change a single column of "X47551;X85H52;X14J56" into three columns of "X47551", "X85H52", and "X14J56".

share|improve this answer

Your Answer


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.