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'?


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".

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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