# Excel Formula Question (Non-Numerical) **URL:** \ **Category:** Factual Questions\ **Created:** [October 10, 2011, 1:41pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172 "2011-10-10T13:41:10Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![35\_U.S.C](https://avatars.discourse-cdn.com/v4/letter/3/a4c791/32.png) [@35\_U.S.C](https://boards.straightdope.com/u/35_U.S.C)\ **Post date:** [October 10, 2011, 1:41pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/1 "2011-10-10T13:41:10Z")
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! ---
**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\ **Post date:** [October 10, 2011, 1:49pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/2 "2011-10-10T13:49:23Z")
=A1&B1&C1&D1&E1 Then copy down column K. Crude but effective. ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 10, 2011, 2:00pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/3 "2011-10-10T14:00:05Z")
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. ---
**Author:** ![Kiber](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kiber/32/317_2.png) [@Kiber](https://boards.straightdope.com/u/Kiber)\ **Post date:** [October 10, 2011, 2:12pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/4 "2011-10-10T14:12:31Z")
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. ---
**Author:** ![35\_U.S.C](https://avatars.discourse-cdn.com/v4/letter/3/a4c791/32.png) [@35\_U.S.C](https://boards.straightdope.com/u/35_U.S.C)\ **Post date:** [October 10, 2011, 2:15pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/5 "2011-10-10T14:15:35Z")
Thank you all for the responses! I will try to implement one (or more) of them, and I’ll report back later today. Cheers! ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [October 10, 2011, 2:20pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/6 "2011-10-10T14:20:07Z")
> [@batsto](#): > > 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. 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. ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 10, 2011, 3:05pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/7 "2011-10-10T15:05:40Z")
> [@don\_t\_ask](#): > > 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. 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. ---
**Author:** ![35\_U.S.C](https://avatars.discourse-cdn.com/v4/letter/3/a4c791/32.png) [@35\_U.S.C](https://boards.straightdope.com/u/35_U.S.C)\ **Post date:** [October 10, 2011, 3:21pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/8 "2011-10-10T15:21:18Z")
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. 😃 ---
**Author:** ![35\_U.S.C](https://avatars.discourse-cdn.com/v4/letter/3/a4c791/32.png) [@35\_U.S.C](https://boards.straightdope.com/u/35_U.S.C)\ **Post date:** [October 10, 2011, 3:22pm UTC](https://boards.straightdope.com/t/excel-formula-question-non-numerical/599172/9 "2011-10-10T15:22:37Z")
Is there any one single Excel book or site that is worth purchasing/bookmarking?