Can this be done in Excel?
#1
Scooby Regular
Thread Starter
Join Date: Aug 2002
Location: not forgetting 20,000 posts from last time ;)
Posts: 5,806
Likes: 0
Received 0 Likes
on
0 Posts
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) ?
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) ?
#2
Moderator
iTrader: (5)
Join Date: Nov 2001
Location: Not all those who wander are lost
Posts: 17,863
Received 0 Likes
on
0 Posts
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.
#4
Moderator
iTrader: (5)
Join Date: Nov 2001
Location: Not all those who wander are lost
Posts: 17,863
Received 0 Likes
on
0 Posts
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.
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.
#5
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
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
=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
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
bluebullet29
General Technical
9
05 October 2015 02:17 PM