Tennisguuy
Well-known Member
- Joined
- Oct 17, 2007
- Messages
- 564
- Office Version
- 2016
- Platform
- Windows
Is there a formula or conditional format you can use to extract and string from a cell where the string is always a certain number of characters. I have a spreadsheet where the cell includes the VIN for an auto but the VIN is always 17 characters long. The problem is there are other items in the cell.
For example here a list of a couple of the cells.
93 2016 Mack CHU613 1M1AN07Y6GM024968 $3,000 $3,000 $124,180
94 2016 Mack CHU613 1M1AN07Y5GM024007 $3,000 $3,000 $124,180
98 2016 CHEVROLET SILVERADO 25 1GC2KUEG2GZ406383 $1,000 $2,000 $40,330
1072016 CHEVROLET SILVERADO 25 1GC1KUEG0GF207328 $1,000 $2,000 $41,085
1082017 CHEVROLET Silverado 15 3GCUKREC2HG331740 $1,000 $2,000 $46,400
The data is in cell X2:x300. I only want to extract the Vin which is always 17 characters. In first line of the example the vin would be 1M1AN07Y6GM024968. However as you can see the while the vin is always 17 characters there other data varies.
I tried using MID, Left and Right but was wondering if there was something I could do to extract just the 17 characters from each cell
For example here a list of a couple of the cells.
93 2016 Mack CHU613 1M1AN07Y6GM024968 $3,000 $3,000 $124,180
94 2016 Mack CHU613 1M1AN07Y5GM024007 $3,000 $3,000 $124,180
98 2016 CHEVROLET SILVERADO 25 1GC2KUEG2GZ406383 $1,000 $2,000 $40,330
1072016 CHEVROLET SILVERADO 25 1GC1KUEG0GF207328 $1,000 $2,000 $41,085
1082017 CHEVROLET Silverado 15 3GCUKREC2HG331740 $1,000 $2,000 $46,400
The data is in cell X2:x300. I only want to extract the Vin which is always 17 characters. In first line of the example the vin would be 1M1AN07Y6GM024968. However as you can see the while the vin is always 17 characters there other data varies.
I tried using MID, Left and Right but was wondering if there was something I could do to extract just the 17 characters from each cell