I have a huge spreadsheet with lists of personal details, names, addresses etc etc
Each person also has a unique id number and this is in the format of xx-yy-zzzz where x,y and z are numbers.
xx is store identifier number
yy is store employee number
zzzz is the customer number
I want to sort all the data in customer number, I know how to normally do Data, Sort but how do I get the spreadsheet to basically ignore the xx-yy- and just sort based on the zzzz?
Hope that makes sense and that someone can help as this is tonight's homework!!
Cheers
|
|
Create another column where if the id is in An then the contents are RIGHT(An,4). This returns the last 4 characters (zzzz) of the id. Then sort on the new column?
|
Might be better to use VALUE(RIGHT(An,4)) to convert the string of digits (zzzz) into a number, although as long as there are always 4 digits, sorting alphanumerically and sorting numerically will give the same results.
If there aren't always 4 digits in zzzz then of course RIGHT(An,4) isn't sufficient.
|
The last digits are either in 3 number format or 4 number.
Excuse the ignorance but I have tried putting
VALUE(RIGHT(An,4)) into the cell where An is the column and row cell, but thas has not returned anything at all other than just showing VALUE(RIGHT(An,4)) in the cell?
|
OK,have put an = before Value and that seems to be working to the extent that I now have either a 3 digit number with a - before it, or the 4 digit number.
Getting there.....
|
Bingo - have done it by repeating the above to take the last 3 digits, then copying value and we are there!
Thanks Focus!
|
Sorry Bobby - should have been more explicit and mentioned the =.
Don't quite follow how you have coped with both the 3 and 4 digit formats, unless you have just used different formulas with either a 3 or a 4 as appropriate? If so I can see how that works, but ideally you would use one formula that could handle both formats. I'm sure there's a way...
Last edited by: Focus on Wed 7 Mar 12 at 20:36
|
>> I'm sure there's a way...
If xx and yy always contain 2 digits, then you can just use:
=VALUE(MID(An,7,4))
which takes the last 4 characters starting from character 7, but if there are only 3 characters it will just take those.
If xx and yy might only be single digits, then you could use FIND() to find the index of the '-' before the zzzz, but it's not trivial because there's 2 '-'.
|
I used your original formula and that gave me a resulting column with numbers in
-xxx and xxxx format
I then highlighted all the -xxx ones and copied into a new cell, using your formula again but changing the 4 to 3 so that gave me the xxx.
I then copied these and paste special them back to original column so that I then had the column with all numbers in either xxx or xxxx format.
Then I data sorted them!
|
|
Got it, ta. A single formula that works with all formats would be better in a sort of pure sense, but if you already have something that does the job then 'if it ain't broke don't fix it' is a valid approach :)
|
|
Thanks for your help Focus.
|
Excel - help with number formats - Focusless
