I know it sounds goofy, but I'm building a spreadsheet from a database system
and the database query only recognizes a double quote as a delimiter. So if
I want to create a column containing the department code/name for each
employee, I need to embed the department code/name into the formula, because
I can't have variables in the database query. The sample table below is:

emp department code/name
------------ ----------------------------
001 1a3dk Process Manager
003 2Kdk3 Area Manager, Special Projects
1333 93kd9 Shift Supervisor

I need to populate the cell in the spreadsheet with the name of the
department only, not the code...so my query looks like

sql select emp, "=RIGHT(chr(34)& [database value for department code/name]
&chr(34),LEN(chr(34)& [database value for department code/name]
&chr(34))-FIND(chr(32),chr(34)& [database value for department code/name]
&chr(34),1))" from............

as each row is built by the query the department code/name is inserted into
the =RIGHT statement...

using the first row in the sample above, the resulting query would produce
the spreadsheet with the following in each column:
col1 col2
-------- ---------------------
001 1a3dk Process Manager
003 2Kdk3 Area Manager, Special Projects
1333 93kd9 Shift Supervisor

=RIGHT(chr(34)& 001 1a3dk Process Manager &chr(34),LEN(chr(34)& 001 1a3dk
Process Manager &chr(34))-FIND(chr(32),chr(34)& 001 1a3dk Process Manager
&chr(34),1))
field99 and so on and so on

and once the spreadsheet is opened, it will recognize the =RIGHT as a
formula and Process Manager would appear in the col2

I know this is very confusing, but trust me, I need to embed the value I'm
performing the =RIGHT function on in the formula and cannot reference a
cell...

thanks in advance for your help.


"Niek Otten" wrote:

Hi Peter,

Enter "12345 description for code 12345" (without the quotes) in a cell.
=RIGHT(A1,LEN(A1)-FIND(CHAR(32),A1,1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"PeterM" wrote in message ...
| Is there a way in Excel 2003 to build the following formula WITHOUT using "'s
| (double quotes)?
|
| =RIGHT("12345 description for code 12345",LEN("12345 description for code
| 12345")-FIND(chr(32),"12345 description for code 12345",1))
|
| I've tried replacing the double quotes with chr(34)& like the example below,
| but Excel doesn't like it...
|
| =RIGHT(chr(34)& 12345 description for code 12345 &chr(34),LEN(chr(34)& 12345
| description for code 12345 &chr(34))-FIND(chr(32),chr(34)& 12345 description
| for code 12345 &chr(34),1))
|
| thanks in advance for your help