17

I have the following formula in excel

=CONCATENATE("insert into #UpdateData (mondayopenhour, mondayopenmin,mondayclosehour, mondayclosemin,tuesdayopenhour, tuesdayopenmin,tuesdayclosehour, tuesdayclosemin,wednesdayopenhour, wednesdayopenmin, wednesdayclosehour, wednesdayclosemin,thursdayopenhour, thursdayopenmin,thursdayclosehour, thursdayclosemin, fridayopenhour, fridayopenmin, fridayclosehour, fridayclosemin, saturdayopenhour, saturdayopenmin, saturdayclosehour, saturdayclosemin, sundayopenhour, sundayopenmin, sundayclosehour, sundayclosemin values ('",TRIM(A2),"',",MID(B2,1,2),",",MID(B2,3,2),",",MID(C2,1,2),",",MID(C2,3,2),",",MID(D2,1,2),",",MID(D2,3,2),",",(MID(E2,1,2),",",MID(E2,3,2),",",MID(F2,1,2),",",MID(F2,3,2),",",MID(G2,1,2),",",MID(G2,3,2),",",MID(H2,1,2),",",MID(H2,3,2),",",MID(I2,1,2),",",MID(I2,3,2),",",MID(J2,1,2),",",MID(J2,3,2),",",MID(K2,1,2),",",MID(K2,3,2),",",MID(L2,1,2),",",MID(L2,3,2),",",MID(M2,1,2),",",MID(M2,3,2),",",MID(N2,1,2),",",MID(N2,3,2),",",MID(O2,1,2),",",MID(O2,3,2),")")

When I try run it i get the following error

Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&).

I have been looking this up online but have got no real solutions for this! does anybody know how to solve this?

Hip Hip Array
  • 4,665
  • 11
  • 49
  • 80

6 Answers6

10

I just hit this problem too... but its not really a problem. You can only have 255 chars in a cell, but unlimited in a formula. Your formula will result in an error, so excel is reading it as text rather than a formula. Find your error and your formula will work.

kirsti
  • 101
  • 2
9

Now this is one messy function. You have to seperate your input to CONCATENATE.

Right now you have there only one very big string starting on "insert ..." ending on "....MID(O2,3,2),")" - or at least, that is where I think this ends, because of all the "," it's really tough to step through this.

Here is how you should use CONCATENATE:

=CONCATENATE("insert into #UpdateData (mondayopenhour,","mondayopenmin,")

You can extend this, so you won't have a single string which is longer than 255 characters.

edit: by the way - your current string has about 972 characters.

you can ease the seperation by using this formula:

=MID("insert into #UpdateData (mondayopenhour, mondayopenmin,mondayclosehour, mondayclosemin,tuesdayopenhour, tuesdayopenmin,tuesdayclosehour, tuesdayclosemin,wednesdayopenhour, wednesdayopenmin, wednesdayclosehour, wednesdayclosemin,thursdayopenhour, thursdayopenmin,thursdayclosehour, thursdayclosemin, fridayopenhour, fridayopenmin, fridayclosehour, fridayclosemin, saturdayopenhour, saturdayopenmin, saturdayclosehour, saturdayclosemin, sundayopenhour, sundayopenmin, sundayclosehour, sundayclosemin values ('",TRIM(A2),"',",MID(B2,1,2),",",MID(B2,3,2),",",MID(C2,1,2),",",MID(C2,3,2),",",MID(D2,1,2),",",MID(D2,3,2),",",(MID(E2,1,2),",",MID(E2,3,2),",",MID(F2,1,2),",",MID(F2,3,2),",",MID(G2,1,2),",",MID(G2,3,2),",",MID(H2,1,2),",",MID(H2,3,2),",",MID(I2,1,2),",",MID(I2,3,2),",",MID(J2,1,2),",",MID(J2,3,2),",",MID(K2,1,2),",",MID(K2,3,2),",",MID(L2,1,2),",",MID(L2,3,2),",",MID(M2,1,2),",",MID(M2,3,2),",",MID(N2,1,2),",",MID(N2,3,2),",",MID(O2,1,2),",",MID(O2,3,2),")",2,255)
Jook
  • 4,564
  • 3
  • 26
  • 53
  • im not sure if `=PART` works but i managed to solve it by breaking the text to pieces and then piecing it back together cell by cell using `= C2 & C3 & C4 & C5` – Hip Hip Array Sep 12 '12 at 11:46
  • @HipHipArray yes - you can use `&` too, but you don't have to seperate the text into single cells, you can use `&` also with strings. But I am sure, seperation will help in any further review. Also you could name your Cells (e.g. `MONDAYS` instead of `C2`), this might help you even further to know what is behind `C2`. – Jook Sep 12 '12 at 12:07
  • It's a long time since this question was asked, but what is "=PART()" function? I can't find it defined anywhere...? – Sancarn Jan 16 '17 at 16:12
  • @Sancarn: sorry, I guessed a bad translation, its MID oder MIDB: https://de.excel-translator.de/teil/ – Jook Mar 15 '17 at 12:15
  • Oh right (lol). I'm honestly surprised this functionality hasn't been changed by Microsoft by now... – Sancarn Mar 15 '17 at 18:57
  • Does it work with in-cell-formating, I mean parts of the text are bold for example? – lalebarde Feb 17 '22 at 17:24
8

Well as for my solution for the same problem, those long text I put it on a separate cell and not part of my formula itself. Example:

=CONCATENATE("INSERT INTO CAS_ACD (ACD_NUMBER, WAGON_WHEEL_INDEX, WAGON_WHEEL_COUNT, WAGON_WHEEL_COUNT_MAX, WAIT_TIME, MAX_WAIT_TIME,
MON_TIME, TUE_TIME, WED_TIME, THU_TIME, FRI_TIME, SAT_TIME, SUN_TIME, DATE_CLOSED1,
DATE_CLOSED2, DATE_CLOSED3, DATE_CLOSED4, DATE_CLOSED5, DATE_CLOSED6, DATE_CLOSED7, DATE_CLOSED8,
DATE_CLOSED9, DATE_CLOSED10, DATE_CLOSED11, DATE_CLOSED12, DATE_CLOSED13, DATE_CLOSED14,
DATE_CLOSED15, DATE_CLOSED16, DATE_CLOSED17, DATE_CLOSED18, DATE_CLOSED19, DATE_CLOSED20,
CTI_SERVER_ADDR, PROGRAM_NUM, VERSION_NUM, COMMENTS, TOD_VOICE_DIR, TOD_VS, ACD_NAME) 
VALUES ("B2, C2,");")

Instead, I store this to one of the cell A2

Cell A2 now contains=
INSERT INTO CAS_ACD (ACD_NUMBER, WAGON_WHEEL_INDEX, WAGON_WHEEL_COUNT, WAGON_WHEEL_COUNT_MAX, WAIT_TIME, MAX_WAIT_TIME,
MON_TIME, TUE_TIME, WED_TIME, THU_TIME, FRI_TIME, SAT_TIME, SUN_TIME, DATE_CLOSED1,
DATE_CLOSED2, DATE_CLOSED3, DATE_CLOSED4, DATE_CLOSED5, DATE_CLOSED6, DATE_CLOSED7, DATE_CLOSED8,
DATE_CLOSED9, DATE_CLOSED10, DATE_CLOSED11, DATE_CLOSED12, DATE_CLOSED13, DATE_CLOSED14,
DATE_CLOSED15, DATE_CLOSED16, DATE_CLOSED17, DATE_CLOSED18, DATE_CLOSED19, DATE_CLOSED20,
CTI_SERVER_ADDR, PROGRAM_NUM, VERSION_NUM, COMMENTS, TOD_VOICE_DIR, TOD_VS, ACD_NAME) 
VALUES ('

So my formula now is

=CONCATENATE( A2, B2, C2)
Timothy
  • 2,004
  • 3
  • 23
  • 29
Nik
  • 81
  • 1
  • 1
4

The easisest way to solve this would be to use some cells to build separate elements of the string and merge them in one cell using concetanate. It provides more room for error solving - maybe your statement has errors, but they would be all but invisible doe to their length.

Christian Sauer
  • 10,351
  • 10
  • 53
  • 85
2

I had this problem with "over 255 char" and solved it without CONCATENATE easily:

I put into cell T1 the long string of Values. Then my Formula was as below:

="INSERT INTO sql_table_name ("&T1&") VALUES ('"&A40&"','"&B40&"','"&C40&"','"&D40&"','"&E40&"','"&F40&"','"&G40&"','"&H40&"','"&I40&"','"&J40&"','"&K40&"','"&L40&"','"&M40&"','"&N40&"','"&O40&"','"&P40&"','"&Q40&"','"&R40&"','"&S40&"');"
Matt Cremeens
  • 4,951
  • 7
  • 38
  • 67
Locom
  • 21
  • 1
1

I came across the problem of 255 characters while using a formula I used the "&" operator in between the String and the problem is solved.

Aswini
  • 11
  • 1