Excel: Use formula longer that 255 characters

Question

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?

score 10 · Answer 1 · answered Oct 22 '12 at 15:27

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.

Jook · Accepted Answer · 2017-03-15T12:15:40.273

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)

edited Mar 15 '17 at 12:15

answered Sep 12 '12 at 11:31

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

score 8 · Answer 3 · edited Jun 28 '16 at 15:18

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)

score 4 · Answer 4 · answered Sep 12 '12 at 11:28

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.

score 2 · Answer 5 · edited Sep 08 '16 at 15:17

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

score 1 · Answer 6 · answered Oct 27 '22 at 09:21

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.

answered Oct 27 '22 at 09:21

Aswini

11
1

sir, your comment is underrated, thanks a lot for your contribution – Oscar Ortiz Aug 08 '23 at 22:01

Excel: Use formula longer that 255 characters

6 Answers6

Linked