Extraction of specific data after # Combining multiple worksheets to master file

Hi

Have some cells that I need to extract some data from.

Iron Man #4
Iron Man #5
Iron Man #6

I need to just extract the no after # up to 3 to 4 digits long.

While I am here also, I have 3 spread sheets that only common factor is an order reference no.

ie. AUG1123456

How can I combine the date so it matches up perfectly in a master file.

ie

Spreadsheet 1 has AUG1123456 Iron Man 5
Spreadsheet 2 has AUG1123456 ISBN No 12354654546498
Spreadsheet 3 has AUG1123456 Tony Stark dies in a battle with hulk

I want it to look like AUG1123456 Iron Man 5 12354654546498 Tony Stark dies in a battle with hulk

in the master file

Thanks

Try his, copied down...

=MID(A1,SEARCH("#",A1,1)+1,99)

If you want the result to be a value you can calc with...
=MID(A1,SEARCH("#",A1,1)+1,99)*1

Did you upload the right file? That 1 has nothing to do with what you poasted above, and also - as far as i can see - contains no formulas at all?

is it combining into one sheet, do I need another thread for this.

So far I am about here

=LOOKUP(A1:A6,Sheet2!A1:A6,Sheet2!B3:B6)

Its the 2nd part to my Question.

All I need to do is take the price from one sheet and get it into the correct section based on Code

OK, Based on the data provided, if its the values in orange you want returned, try this, copied down...

=VLOOKUP(A3,Sheet1!$A$3:$B$6,2,0)

If you have more columns of data you need to return, adjust the range (Sheet1!$A$3:$B$6) as needed, and increase the column number (,2,)

Just fyi, in this part...
SEARCH("#",A1,1)+1
the search is for #, but you wanted the very next character, so what that does, is find the postion, starting from the 1st character (,1), and then adds 1 more to it.

So if you want to pull more than just 1 character away from the #, then yes, you could increase the +1 by however many characters you need to "skip"

best advise...if it's working and you want to modify it...try it and see where you get