Forum Discussion

YDNIW3791's avatar
YDNIW3791
Copper Contributor
May 12, 2023

Formula or Function

I am needing to know if it is possible in Excel to search a column of data and pull a section of that data if it matches certain rules and have it copied into another column.

example  in my spreadsheet which was imported from another program it has placed a description of inventory transactions all in one column labeled description.  This column contains the year make model and last six of the VIN all with only a space no commas.  And I have over 2000 rows of this that makes it impossible to sort when I am trying to reconcile accounts searching for errors.  I would like to be able to pull the part of the description with the last six of the VIN from every row in the column on the spreadsheet and have the last six of the VIN duplicated or even removed and placed in another column.  The last six of the VIN part of the description always begins with "VN#" followed by 6 characters.  Is this possible and if so how?

 

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 12, 2023

    YDNIW3791 

    I'm sure it is possible. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • YDNIW3791's avatar
      YDNIW3791
      Copper Contributor
      May 12, 2023
      yes this is a row of my data it has the date in one column next column is blank and 3rd is the column that i need to pull the part that has the VN#...... is what i need to copy or move to the 2nd column that is blank . However every row in the 3rd column is different but every row contains the VN#....
      21 Apr 2022 Credit Acceptance Corp - 2015 Chrysler 200 Gray VN#538798
      04 Aug 2022 WISEROADS - 1998 Dodge Ram VN#190748 (TI)
      20 Dec 2022 Accelerating the Future Auto Sales - 2007 Ford F150 VN#D74721 (TI)
  • sanjibdutta's avatar
    sanjibdutta
    Brass Contributor
    May 13, 2023
    MY quick solution is like this following..
    If your data are spread from and below A2,B2,C2(It may be others) then you can try putting formula "==IFERROR(MID(C2,SEARCH("VN#?????? ",C21),9),"No VN#")" in cell B2 and coppy(drag) the formula below till the end of the table it should hopefully copy VN# in column B. If no VN# is present in column C it would display "No VN#" for that cell(You can customize this error message"). You may directly remove VN# from column C because column B depends on the presence of VN# in column C. But If you want column C values in a different column like E you can place formula "=SUBSTITUTE(C2,B2,"")" in column E2..Thanks

Resources