Extract string of text from a cell that is always an exact number of strings

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
541
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
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,606
Office Version
365, 2016
Platform
Windows
Assuming that the first non space character after the VIN is always a $ try
Code:
=MID(X2,SEARCH("$",X2)-18,17)
 
Last edited:

Forum statistics

Threads
1,084,972
Messages
5,380,938
Members
401,706
Latest member
ppilot

Some videos you may like

This Week's Hot Topics

Top