How to split text into columns based on a specific number of characters

Question

Hello,

I have a very large excel file with thousands of ISIN numbers which I need to split into 2 columns. The ISIN numbers are 12 characters long and I need the first 9 characters in one column and the remaining 3 in a second column. For example, for the ISIN number US38259P5089, I need the first column to have US38259P5 and in the second column I need 089. I have included some sample data. Is it possible to do this?

Any help would be greatly appreciated.

Thank you!

ISIN Numbers (Sample).xlsx

Claje · Accepted Answer

If the data is consistent like this (always 12 characters), two Formulas will do this.

A quick example of the formulas

Col1:

LEFT([SourceField],9)

Col2:

RIGHT([SourceField],3)

If you have data quality issues, there are some more complex formulas that will handle them better, but I would start here.