Sorting VIN numbers.

**Nemesis708** · 10-21-2009, 10:47 AM

Hello and thanks for viewing.

I have a bunch of excel spreadsheets full of car VIN numbers and they were all entered manually. They are all supposed to be 17 characters long, but sometimes they are mistyped and end up being less or more than 17. I cant just sort ascending or filter because its not a visual problem. they all look different because of the different numbers and stuff.

Can someone please help me find a formula I can use to count each and every VIN in the column and sort them.

Thanks again for viewing.

PS. using Excel 2003

**Ron Coderre** · 10-21-2009, 10:53 AM

How do you want the irregular VIN numbers handled?
...please give some examples.

Also, can we assume that VIN "numbers" are actually alpha-numeric?

**Nemesis708** · 10-21-2009, 10:55 AM

3D7MX48C16G150213
2A8HR44E69R516378
1D4HB58236F129150
2G2WP5527811742<--- not enough characters.
1B3HB28BX8D697763
2C4GM68475R368010
5GAET13M472141 <-wrong
3C8FY68BX5T536569

If I can I would like them to be sorted into an asceding/descending order. I just need a count on each cell in the column making sure they are 17 characters long.

**martindwilson** · 10-21-2009, 11:34 AM

perhaps in another column dragged down
=REPT("#",17-LEN(A1))&A1 then sort by that column

**Nemesis708** · 10-21-2009, 12:10 PM

Im working with colum j2 - j25 what would I put the formula in as?
=REPT("#",17-LEN(j2))&j25

**martindwilson** · 10-21-2009, 05:02 PM

=REPT("#",17-LEN(j2))&j2 in say k2 then drag down

**bobnmona** · 10-14-2015, 06:34 PM

Originally Posted by Ron Coderre

How do you want the irregular VIN numbers handled?
...please give some examples.

Also, can we assume that VIN "numbers" are actually alpha-numeric?

Hello I have a situation where I have 101 rows and need to sort the vin column by the last 5 digits only is this possible? thank you in advance.

Hello Unit # Qty Rcvd RMS Stk # Make Model Spec # Ord.# Ordered VIN Ship To / Upfitter

1 of 101 W4236 Chevy 2500 242FG02 SQNC30 6/2 1GB0CUEG1FZ548375 Phenix Stock w/switches
2 of 101 W4251 Chevy 2500 242FG02 SQNC31 6/2 1GB0CUEG9FZ548494 Phenix Stock w/switches
3 of 101 W4278 Chevy 2500 242FG02 SQNC32 6/2 1GB0CUEG3FZ548801 Phenix Stock w/switches
4 of 101 W4257 Chevy 2500 242FG02 SQNC33 6/2 1GB0CUEG0FZ548674 Phenix Stock w/switches
5 of 101 W4281 Chevy 2500 242FG02 SQNC34 6/2 1GB0CUEG2FZ548868 Phenix Stock w/switches
6 of 101 W4289 Chevy 2500 242FG02 SQNC35 6/2 1GB0CUEG9FZ548754 Phenix Stock w/switches
7 of 101 W4267 Chevy 2500 242FG02 SQNC36 6/2 1GB0CUEG5FZ548587 Phenix Stock w/switches
8 of 101 W4220 Chevy 2500 242FG02 SQNC37 6/2 1GB0CUEGXFZ548228 Phenix Stock

**Richard Buttrey** · 10-14-2015, 06:39 PM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member and especially one that's 6 years old -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.