How do I replace multiple cells of text with the first Number value above them?

[patrickmg17]

that	this	another	some more	again	lastly
86468​	4654​	54654​	651651​	Note 1	65168​
Note 2	6546+4	84615​	Note 2	32131​	6545​
Note 2	321651​	98461​	651​	65165​	65651​
Note 2	651651​	651651​	6516511​	6516​	651​
61651​	321651​	Note 1	4984436​	31816​	354684​
161961​	49649846​	65464​	32133​	Note 2	6546514​
619496​	164​	6.52E+08​	3131​	Note 2	351651​

I have the above cells as an example. I want to replace every instance of "Note 2" with the number value above it. So for the first column all 3 "Note 2"s should say 86468. And the ones in column 5 should be 31816. This is the most important part i need, so if you can answer that I'm happy but there is another part i need to figure out as well; for every instance of a row having a "Note 1" in it, i need that whole row of data deleted. Most of these rows are were the Note 2 spots pull their replacements so this part has to come after they are replaced.

Using macros or just some simple replace coding would be great, the sheets i go through are 2000 rows long so keep that in mind, THANK YOU :D

 

[patrickmg17]

I'm confused, are you saying you need to this without enabling macros?

Yeah Im working this on multiple computers but the main one is in excel 2016 and cant enable any add-ins so that means i cant run any macros to solve it

 

[Akuini]

Without macro then the alternative is formula. However, formula can't delete cell or row, so I don't think you can do it with formula.

 

[patrickmg17]

I'm confused, are you saying you need to this without enabling macros?

I work between different computers and the main one this needs to run on uses excel 2016 and is blocked from using add-ins including macros so I need a new solution with out using any

 

[patrickmg17]

So theres no feasible solution? could it just rewrite the cells in another sheet? So it doesn't 'delete' the lines, it just doesn't write those lines

 

[Akuini]

So theres no feasible solution? could it just rewrite the cells in another sheet? So it doesn't 'delete' the lines, it just doesn't write those lines

If the result would be in another sheet, I think it's possible with formula. However, I have only basic knowledge of Excel formula. So, I hope somebody else will step in.

 

[Joe4]

uses excel 2016 and is blocked from using add-ins including macros so I need a new solution with out using any

Just wanted to clarify one thing - Excel Macros/VBA are NOT an Add-In.
You do not need to do/add anything special to have/use them - just enable them.
I understand that for Security reasons, many companies may not allow them. But just wanted to clarify that they are something entirely different than Add-Ins you may add on.

It sounds like you may be stuck in between a rock and a hard place - you want the functionality that Macros/VBA offer, but without using Macros/VBA.
I think any potential formula solution is going to be a bit complex/clunky and have to return the data to a new sheet (as formulas cannot delete rows, as was mentioned previously).
You may want to take KerryX on their suggestion to possibly make use of Power Query which was added to Excel with Excel 2010.
There is a whole forum here devoted to tools like Power Query called "Power Tools".