If Column B says "1234 xyz" then Column O will return "This for Example"

[hntp]

I want to create a macro that will search for a certain number in column B and if that number is there then enter certain text in column O.

Examples:

In column B I have "2964 MUSIC CITY CENTER - TN"
In Column O I want it to say "Moved to NSH #1000"

In column B I have "3084 RED BANK MIDDLE SCHOOL"
In Column O I want it to say "Moved to NSH #1008"

The variables will be what is in column B and what column O returns.
This is an aging report that comes from my accounting department. Some of the jobs have moved to a different report and I want to note that information each month without having to retype this in column O each month.

I have a list of job numbers that moved (this info does not change) and I have a list of the new job numbers they have moved to.

Example: 2964 is now 1000, 3084 is now 1008, etc.

If you could help me understand how to create this because I have different reports from different states and the range of numbers vary for each state.

Thank you very much.

[Max, Singapore]

Try this formula solution ..

Assume that in say, Sheet2's A2:B2 down,
you have/maintain this kind of list
2964 1000
3084 1008
etc

Then in your source data sheet,
Assuming the data such as:"2964 MUSIC CITY CENTER - TN" runs in B2 down,
you could place this in O2, normal ENTER to confirm will do:
="Moved to NSH # "&INDEX(Sheet2!$B$2:$B$100,MATCH(TRUE,INDEX(Sheet2!$A$2:$A$100&""=LEFT(B2,SEARCH(" ",B2)-1),),0))
Copy O2 down as far as required


And should you need to return neater looking blanks:"" instead of ugly #N/As for any unmatched cases (including blank source cells), you could use this ISNA error-trap version instead in O2, normal ENTER to confirm, copied down:
=IF(ISNA(MATCH(TRUE,INDEX(Sheet2!$A$2:$A$100&""=LEFT(B2,SEARCH(" ",B2)-1),),0)),"","Moved to NSH # "&INDEX(Sheet2!$B$2:$B$100,MATCH(TRUE,INDEX(Sheet2!$A$2:$A$100&""=LEFT(B2,SEARCH(" ",B2)-1),),0)))

-------
Success? Celebrate it, do click the little star at the bottom left of my response

[hntp]

There are lines in between the line with the job name. See attached.
And this data changes monthly based on the reports I get from our accounting department. So the information is not on the same lines each month.

Thanks.