Excel formula question

I put together spreadsheets with cable ID information for
uploading 
into our database. The cables are broken down into
individual columns 
for Bldg TR, Room#, Seq.#, D or V. From these columns I want
to 
create a column that indicates the cableID as it might
appear on a 
label. For example MOFFIT-312-321-005-D
To get this I use the Concatenate command: 
=CONCATENATE(S3,"-",T3,"-",U3,"-&qu
ot;,V3,"-",W3)
This takes the value in each column and inserts a dash
between them.

My problem is that not all cable ID's (older one) have all
columns. 
For example MOFFIT-201-1
In this case I end up getting double dashes or a dash at the
end so 
it ends up looking like this: MOFFIT--201-1-

Does anyone know what else I can add to the formula so it
doesn't 
double up the dashes or add to the end?

Thanks in advance for assistance.

Monica


------------------------------------------------------------
------------
The following was automatically added to this message by the
list server:

For information about Micronet, including subscribing to
or unsubscribing from its mailing list and finding out
about upcoming meetings, please visit the Micronet Web site:
<http://micronet.be
rkeley.edu/>.

It will be a long formula, but you could insert an IF() to
test for a 
non-empty field, and only insert the dash for non-empty, and
an empty 
string ("") where it is not needed.

...,IF(ISEMPTY(cellref), "", "-"),... 
(very roughly from memory).

Graham

--
Graham Patterson, System Administration
Dept. of Economics, UC Berkeley (510)643-5397


------------------------------------------------------------
------------
The following was automatically added to this message by the
list server:

For information about Micronet, including subscribing to
or unsubscribing from its mailing list and finding out
about upcoming meetings, please visit the Micronet Web site:
<http://micronet.be
rkeley.edu/>.

Hi Monica,

The most direct solution: you can try putting an IF
condition on each "-".

For example:

=CONCATENATE(S3,IF(T3="","","-"
;),T3,IF(U3="","","-"),U3,IF(V
3="","","-"),V3,IF(W3="&q
uot;,"","-"),W3)

or some variation of this formula.

Curtis

Monica Mack wrote:
> I put together spreadsheets with cable ID information
for uploading 
> into our database. The cables are broken down into
individual columns 
> for Bldg TR, Room#, Seq.#, D or V. From these columns I
want to create 
> a column that indicates the cableID as it might appear
on a label. For 
> example MOFFIT-312-321-005-D
> To get this I use the Concatenate command: 
>
=CONCATENATE(S3,"-",T3,"-",U3,"-&qu
ot;,V3,"-",W3)
> This takes the value in each column and inserts a dash
between them.
>
> My problem is that not all cable ID's (older one) have
all columns. 
> For example MOFFIT-201-1
> In this case I end up getting double dashes or a dash
at the end so it 
> ends up looking like this: MOFFIT--201-1-
>
> Does anyone know what else I can add to the formula so
it doesn't 
> double up the dashes or add to the end?
>
> Thanks in advance for assistance.
>
> Monica
>
>
>
------------------------------------------------------------
------------
> The following was automatically added to this message
by the list server:
>
> For information about Micronet, including subscribing
to
> or unsubscribing from its mailing list and finding out
> about upcoming meetings, please visit the Micronet Web
site:
> <http://micronet.be
rkeley.edu/>.


------------------------------------------------------------
------------
The following was automatically added to this message by the
list server:

For information about Micronet, including subscribing to
or unsubscribing from its mailing list and finding out
about upcoming meetings, please visit the Micronet Web site:
<http://micronet.be
rkeley.edu/>.

Thanks to all who provided suggestions. This one from Curtis
seems to 
fit my needs the best. I just tested it and it works
beautifully!

Thanks,
Monica

At 11:54 AM 11/21/2006, Curtis Leung wrote:
>Hi Monica,
>
>The most direct solution: you can try putting an IF
condition on each "-".
>
>For example:
>
>=CONCATENATE(S3,IF(T3="","","-&
quot;),T3,IF(U3="","","-"),U3,
IF(V3="","","-"),V3,IF(W3=&quo
t;","","-"),W3)
>
>or some variation of this formula.
>
>Curtis
>
>Monica Mack wrote:
>>I put together spreadsheets with cable ID
information for uploading 
>>into our database. The cables are broken down into
individual 
>>columns for Bldg TR, Room#, Seq.#, D or V. From
these columns I 
>>want to create a column that indicates the cableID
as it might 
>>appear on a label. For example MOFFIT-312-321-005-D
>>To get this I use the Concatenate command: 
>>=CONCATENATE(S3,"-",T3,"-",U3,&q
uot;-",V3,"-",W3)
>>This takes the value in each column and inserts a
dash between them.
>>
>>My problem is that not all cable ID's (older one)
have all columns. 
>>For example MOFFIT-201-1
>>In this case I end up getting double dashes or a
dash at the end so 
>>it ends up looking like this: MOFFIT--201-1-
>>
>>Does anyone know what else I can add to the formula
so it doesn't 
>>double up the dashes or add to the end?
>>
>>Thanks in advance for assistance.
>>
>>Monica
>>
>>
>>----------------------------------------------------
--------------------
>>The following was automatically added to this
message by the list server:
>>
>>For information about Micronet, including
subscribing to
>>or unsubscribing from its mailing list and finding
out
>>about upcoming meetings, please visit the Micronet
Web site:
>><http://micronet.be
rkeley.edu/>.


------------------------------------------------------------
------------
The following was automatically added to this message by the
list server:

For information about Micronet, including subscribing to
or unsubscribing from its mailing list and finding out
about upcoming meetings, please visit the Micronet Web site:
<http://micronet.be
rkeley.edu/>.