Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Formula or Function

Copper Contributor

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?

 

5 Replies

@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?

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)

@YDNIW3791 

Thanks, but please see my previous reply.

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