Excel: Use formula longer that 255 characters

问题:

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?

回答1:

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)

回答2:

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.

回答3:

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.

回答4:

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)

回答5:

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&"');"