Excel Formula Question (Non-Numerical)

Factual Questions
1

Dopers,

Is there a way to do the following in Excel 2010 (or any other version?)
I have hundreds and hundreds of rows, but…

Simplified:

I have 10 columns.
5 Columns contain data i’m interested in.
For each row, only one of the 5 columns has data in it (a non-numerical string)
I want to write a formula that says "Look in Columns B,C,D,E, and F. If you find
a string there, copy the value of that cell into the corresponding cell in Column K.

Visualized:
-----A----B-----C----D-----E-----F-----G-----H-----I-----J---- K
1--------LG----------------------------------------------------- LG
2----------------YA----------------------------------------------YA
3---------------------PP---------------------------------------- PP
4--------------- RR----------------------------------------------RR
5---------------------QT----------------------------------------QT
6----------------------------LY--------------------------------- LY
7--------UP---------------------------------------------------- UP
8----------------SN-------------------------------------------- SN
9--------FX-----------------------------------------------------FX
Is there a formula I could put in for column K to accomplish this?

THANKS!

2

=A1&B1&C1&D1&E1

Then copy down column K.

Crude but effective.

3

You could concatenate the fields: =concatenate(a2,b2,c2,d2…) (or whatever fields have the data in them). Put that in column K.

But if there is more than one column with data in it for each row, the concatenate will put all the values together in field in column K.

4

You can also do an if/then approach. A bit awkward, but will definitely work. Something like: =IF(A1="",IF(B1="",IF(C1="",IF(D1="",E1,D1),C1),B1),A1)

Put this in whatever cell you want the result copied too, then copy that down.

5

Thank you all for the responses! I will try to implement one (or more) of them, and I’ll report back later today.

Cheers!

6

Quite right.

So I would use =if(len(concatenate(a2,b2,c2,d2…) > 2, “Error”, concatenate(a2,b2,c2,d2…))

Sort out any errors manually.

7

That formula looks like it’s testing for the length of the string formed when you concatenate the various columns to see if it’s greater than 2. But if there are strings of various lengths in the data cells, then they’ll look like errors once they are concatenated. So if the value ‘123’ appears in cell a2, the len value would be 3 and look like an error.

8

I wound up using the concatenate function (without error checking).

It worked perfectly! Thank you all so much, this saved me lots of time.

35 U.S.C. :smiley:

9

Is there any one single Excel book or site that is worth purchasing/bookmarking?