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.

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: office.microsoft.com/en-001/excel-help/…. –  Magnum Mar 5 '13 at 12:56
add comment

1 Answer

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
add comment

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.