Filling a cell by taking data from the spreadsheet name

Good Morning,

I'm looking to create a formula which I wouldn't be suprised if it was impossible but thought I would ask the question as you lot always seem to have an answer...

The order system at my workplace we name every order by order number 18650 COMPANY NAME (DESIGN NAME)

On the first sheet "Factory" we then re-type the DESIGN NAME in to cell C9 without the brackets. Is their a formula I can use to pull the design name from the bracket area of the spreadsheet name? I know this is pretty complex and will understand if its impossible.

Also Cell E7 is the order number so if it is possible could we take the first 5 characters from the name and put this in the order number box?

Thank you
Darren

Hi Darren,

Bob Phillips shows how you can use the CELL function to obtain the filename here:

http://www.xldynamic.com/source/xld.xlFAQ0002.html

You can use:

=LEFT(A1,5)

to extract the first five characters of the name, assuming it is in A1.

Hope this helps.

Pete

Hi cafc_fuller

Assuming: 18650 COMPANY NAME (DESIGN NAME) is in A1, Try.

Formula:

Please Login or Register  to view this content.

PL see the attached file with formulas in Sheet1.

18965 Ross (Test).xlsx

I have used your formulas on the attached spreadsheet but it don't seem to be working?

Thanks
Darren

It doesnt work for the order number or the design name....
Sorry to be a pain but if you do know a formula for this could you have it so if the value is null #VALUE! does not appear?

Thank you once again for your great work.

Darren

Sorry, forgot the order number. =LEFT(A1,FIND(" ",A1)-1)&" "&TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("(",A1)),")",""))

This forumla still doesnt work when I put it into the spreadsheet?

Hi

Perhaps upload a sample sheet with dummy data.

Hi Kevin,

Please look at my post at 5.23 above - sample sheet is attached.

Thanks
Darren

Thank you Pete, sorry I am a little confused as to why it matters that their is nothing in cell A1.
I thought i would have to put the formula which Kevin supplied into the design reference/order number box....

Thanks

Sorry i understand that the values need to be in A1 now and then it can take the data from this.
However how do i get A1 to get the data from the name of the spreadsheet?

Sorry, but what sheet should I be looking at, perhaps it is me.

Yes, but read post #3 again - that formula is expecting this string:

18650 COMPANY NAME (DESIGN NAME)

to be in A1. If that's the name of your file, then you can put the formula that I pointed you to in Bob's site (in post #2) to retrieve the filename in cell A1 (behind your logo).

Hope this helps.

Pete

Yep this works - sorry for the confusion.
The only problem i have is that the name is including .xlsx at the end so the Design reference is displaying as text.xlsx

Is their anyway to remove this?

Thanks
Darren

Use:

=LEFT(A1,LEN(A1)-5)

to remove the extension.

Hope this helps.

Pete

Hi Pete,

I have used the forumla in A1 to take the file name, however this includes the file extension ".xlsx"
This is what I would like to remove as this is included in the design reference box?
It is not just taking the data from the brackets the forumla also takes the file extension?

Thank you
Darren

Sorry i must of typed whilst you did this post thank you so much!

HI Pete,

I am really sorry - i just can't get this to work....

How do i merge these together to stop the file extension appearing?
=TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-5)-FIND("(",A1),")",""))
=LEFT(A1,LEN(A1)-5)

Thanks
Darren

Thank you for all your help everyone, i stopped being stupid and worked it out!
Thank you
Darren