How can I check if a cell contains a certain word, than if it does copy the next numbers to another cell?

Phantasm

Board Regular
Joined
Nov 2, 2007
Messages
58
Cell P15 contains a bunch of job notes like the following:

"PROGRAM # 7046
LVL # 001
PANEL DOOR INNER DIVAN 650
INCLUDES 72.67 INCHES OF 5-AXIS TRIM
CUSTOMER TO SUPPLY .047 IN. THK. CARBON FIBER FORMED PARTS
PART SHARES A COMMON FIXTURE WITH 39593
RESET-UP OF JOB #17052
GREEN TRIM LINE ON CUSTOMER FIXTURE"

What I need to do is get the program number (7046 in this case) and populate textbox1 on a userform with it. Also, I would like to populate textbox 2 with the lvl number (001 in this case). how would I go about doing this? My first thought was to search for the terms "PROGRAM" & "LVL" within the cell, but I dont know how to select what I need after that. Program number isnt always on the first line & I have seen them use "PRG" sometimes, so it gets even more complicated...
 

Some videos you may like

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

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
Whew, that was a bit complex, but think I have something that may work. It's assuming that "PROGRAM #" or "PRG #" precedes the program# and "LVL #" precedes the level#
PROGRAM # 7046 LVL # 001 PANEL DOOR INNER DIVAN 650 INCLUDES 72.67 INCHES OF 5-AXIS TRIM CUSTOMER TO SUPPLY .047 IN. THK. CARBON FIBER FORMED PARTS PART SHARES A COMMON FIXTURE WITH 39593 RESET-UP OF JOB #17052 GREEN TRIM LINE ON CUSTOMER FIXTURE7046001
Bunch a junk PRG # 4076 LVL # 003 PANEL DOOR INNER DIVAN 650 INCLUDES 72.67 INCHES OF 5-AXIS TRIM CUSTOMER TO SUPPLY .047 IN. THK. CARBON FIBER FORMED PARTS PART SHARES A COMMON FIXTURE WITH 39593 RESET-UP OF JOB #17052 GREEN TRIM LINE ON CUSTOMER FIXTURE4076003

<tbody>
</tbody>

In Q15 place:
Code:
=LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(MID(P15,IFERROR(FIND("PROGRAM #",P15),IFERROR(FIND("PRG #",P15),0)),30),"PROGRAM #",""),"PRG #","")),FIND(" ",TRIM(SUBSTITUTE(SUBSTITUTE(MID(P15,IFERROR(FIND("PROGRAM #",P15),IFERROR(FIND("PRG #",P15),0)),30),"PROGRAM #",""),"PRG #",""))) -1)
In R15 place:
Code:
=LEFT(TRIM(SUBSTITUTE(MID(P15,IFERROR(FIND("LVL #",P15),0),30),"LVL #","")),FIND(" ",TRIM(SUBSTITUTE(MID(P15,IFERROR(FIND("LVL #",P15),0),30),"LVL #",""))) -1)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,353
Messages
5,528,199
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top