I have a column in a table which contains an alpha-numeric string. I need to sort by this column, but because some of the numeric part is 3 digits, and some 4, it is sorting ABC1000 before ABC101. Some rows also contain longer text entries, which I can ignore, so the ones I want to work with all start with "ABC"

Is it possible to check the length of the cell contents and if this is 6 characters/digits, and the first three characters are "ABC", then to insert an additional zero as the 4th character (so that ABC100 becomes ABC0100) ?

 

Not sure if it can be done with a function. But personally, I would split the column using text-to-columns into single characters per column, sort by the last column and insert the zeros. Then concatenate them back afterwards.

 

Could you explain how I do the text-to-columns split please?

 

1) Highlight the column in question
2) Ensure you have enough spare, empty columns to the right of the selected column to take the data.
2) Choose Data, Text To Columns
3) Choose Fixed Width
4) Put break points after each character
5) Set them all to text (or else you'll lose your zeros).
6) Job done

Then you can sort by the last column and it will bring all the ones that are short to the top (or bottom). Then you can insert cells and shift to the right and add the required number of zeros. Then use the concatenate function on the columns and cut & paste the values back into the original column.

 

I'd insert a column after the one you want to sort then use a formula such as this
=LEFT(A1,3) & IF(LEN(A1)=6, "0"& RIGHT(A1,3), RIGHT(A1,4))
This takes the first 3 chars (abc) and checks the length of the text. If its 6 then it appends 0 and the 3 chars from the right. If > 6 it appends 4 chars from right.

Copy the formula down the column, sort your data on this column then hide it

HTH

Mark

 

Good one Mark That'll save me some time text-to-columning

 

Thanks both - worked a treat and saved lots of clicking and editing