[Solved] Mixing concatenate and if statements?

[adventurehouse]

I've got data that needs to be massaged prior to exporting to a CSV file for importing. I have a spreadsheet with multiple columns with data and multiple sheets. Such as one sheet has T2 is AUTHOR1, U2 is AUTHOR2, V2 is AUTHOR3 and W2 is AUTHOR4. Not all rows have data in them, but I need to combine these columns that have data into a new column of text, with each column of text separated by a " - ". It's easy to combine these if each column has text...the problem is when a column is missing data.

My formula is: ="AUTHORS: "&TRIM($'Auction Items'.T2)&" ["&TRIM($'Auction Items'.X2)&"]" & " - "&TRIM($'Auction Items'.U2)&" - "&TRIM($'Auction Items'.V2)&" - "&TRIM($'Auction Items'.W2)&" ARTIST: "&TRIM($'Auction Items'.Y2)

Result: AUTHORS: Robert E. Pinkerton [Wi-Di-Go] - S. Omar Barker - Hugh Pendexter - Arthur D. Howden Smith ARTIST: Walter Baumhofer

The problem combination with blank cells results with:
AUTHORS: Steuart M. Emery [The Poison Patrol] - Perry Paul - - ARTIST: Chris Schaare

Those darn extra "-".

I was wondering if I could eliminate those extra spaces and "-" using a IF, in that if the cell doesn't contain text then don't bother adding " - ". But no matter how I try to use this, I come up with an error message.

Please help!

[robleyd]

A sample of what you have tried, and the related error messages, would help.

A simple example that uses the dash if the cell A1 is empty, else the cell value:

Code: Select all

=IF(ISBLANK(A1);"-";A1)

Adapt this to your needs.

[RusselB]

What about

Code: Select all

=trim(substitute("AUTHORS: "&$'Auction Items'.T2)&" ["&$'Auction Items'.X2&"]" & " - "&$'Auction Items'.U2&" - "&$'Auction Items'.V2&" - "&$'Auction Items'.W2&" ARTIST: "&$'Auction Items'.Y2;"- -";""))

As robleyd states, an idea as to what you have tried and the relevant error messages would help, but I think what I have given will handle what you are asking for.
If it doesn't work, please include the results that you actually got and what you actually expected. If there's an error message, be sure to include all of the information from that message.

[adventurehouse]

Thanks gents. My error was using a wrong statement. I've written a ton of dBase code, and have been dabbling with OpenOffice more recently, so I wrote something like: if(T2#" "; T2) Or something just as inelegant. What I really need is to include those cells that are not blank, and then add the " - " if the next cell is NOT blank. If push comes to shove, I could always remove those extra " - " from the CSV once it's saved, but there could be anywhere from 1 to 3 extra " - ", and it's not as tidy.

[RusselB]

My usage of the substitute function should eliminate the extraneous -'s

[adventurehouse]

Thanks...but no. Here is the code as recommended within a cell that has several blank cells:
=TRIM(SUBSTITUTE("AUTHORS: " & 'Auction Items'.T5 & " [" & 'Auction Items'.X5 & "]" & " - " & 'Auction Items'.U5 & " - " & 'Auction Items'.V5 & " - " & 'Auction Items'.W5 & " ARTIST: " & 'Auction Items'.Y5 ;"- -";""))
Here are the results:
AUTHORS: Steuart M. Emery [The Poison Patrol] - Perry Paul - - ARTIST: Chris Schaare

In those cases in which there are more than two blank cells returned the following:
AUTHORS: Kenneth Robeson [Lester Dent] [The Lost Oasis] - - - ARTIST: Walter Baumhofer

[RusselB]

Please attach a sample of your spreadsheet, as we're just giving you best guesses without having a sample to see how your data is actually laid out.
See [Forum]How to attach a document for information regarding attaching a document

[adventurehouse]

I've attached a small sample of the spreadsheet. Thanks for your help.

John Gunnison

[robleyd]

I suggested the use of IF() and ISBLANK() earlier in the topic, so I'll put my solution forward based on those functions. Sheet3 of the attached document has one column which concatenates the author(s)/artist information. It appears to handle the situation of gaps in the author columns - e.g. author2 is blank but author3 has content. Note that author1 must have content as it stands - although it would be easy enough to add a test for blank on that column as well.

You could handle the absence of an artist name similarly allowing you to leave out the word ARTIST if such were needed.

[adventurehouse]

An elegant solution. Although one side question... does the use of "&" not work, where concatenate does?

[RusselB]

I'd recommend using one or the other, just depending on how you want your statement configured.
If the formulas work with CONCATENATE, then they would work (with some modifications) with the &
The modifications would be to allow the usage of the & where the CONCATENATE function uses a ;
There might (I haven't looked at robleyd's spreadsheet) be situations where the usage of the & makes it more difficult to write.

[robleyd]

I chose to use CONCATENATE for the overall joining of text elements, and the shortcut & within the IF statements, to hopefully make the end formula a little easier to read, and distinguish between the outer and inner concatenations.

A tip; when I am developing 'complex' formulae, I use a text editor to allow me to 'pretty print' the formula so I can see the individual elements of the formula. I then copy the formula into Calc. For example, this is how the formula in my uploaded file looks when neatly formatted - each line is one argument to CONCATENATE:

Code: Select all

=CONCATENATE(
   "AUTHORS: ";
    $'Auction Items'.T2;
    " [";
    $'Auction Items'.X2;
    "]";

    IF(ISBLANK($'Auction Items'.U2);"";" - " & $'Auction Items'.U2) ;
    IF(ISBLANK($'Auction Items'.V2);"";" - " & $'Auction Items'.V2) ;
    IF(ISBLANK($'Auction Items'.W2);"";" - " & $'Auction Items'.W2) ;

    " ARTIST: ";
    $'Auction Items'.Y2
)

Of course, CONCATENATE is limited to 30 arguments, where you can use & for larger numbers of strings.

[adventurehouse]

Never thought of that. Looks like dBase coding at that point.

John G.

[robleyd]

Looks like dBase coding at that point.

Oy! No need to be rude about it

[RoryOF]

There are not many of us left who remember dBase!

[robleyd]

And of those few, I suspect some would prefer to forget...