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

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
Office Version
  1. 2016
Platform
  1. 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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Assuming that the first non space character after the VIN is always a $ try
Code:
=MID(X2,SEARCH("$",X2)-18,17)
 
Last edited:
Upvote 0
Thanks Scott that worked perfectly. I really really appreciate your help.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top