# String in Column A to be divided into 2 Strings if leght > 50

#### mesje

##### New Member
Hello All,

I have a set of data in Which in column A is the name of organisation.
If string in col A is longer than 50 I need to split in and put in col B.
That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.
Example:

THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
Incorrect; lengt = 98

THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.

My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.

Any ideas what formulae I can use to get desired effect?

Thank you,
Mesje

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
1st part =LEFT(A1,49+FIND(" ",MID(A1,50,255)))
2nd part=MID(A1,50+FIND(" ",MID(A1,50,255)),255)

• mesje
1st part =LEFT(A1,49+FIND(" ",MID(A1,50,255)))
2nd part=MID(A1,50+FIND(" ",MID(A1,50,255)),255)

WOW - Spot on! Problem solved.

Thank you very much for your help )

WOW - Spot on! Problem solved.

Thank you very much for your help )

Well.. actually I double-checked few records by checking lenght and formula allows them to be longer than 50 in 1st part :/
49+FIND (...) looks up for space after 49th char and then it cuts the string.
It should look up for 50th char; check if it's space; if isn't go to 49 if it isn't go to 48 (loop like that). Once you find space then trim it down in this position.
Any idea?

Thank you,
Mesje

WOW - Spot on! Problem solved.
Are you sure? I thought the first part had to be no more than 50 characters. Am I wrong with that?

If the data was "THIS EXAMPLE NAME IS WAY TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO" then the suggested formula puts 54 characters in the first part.

The formulas suggested also error if the original name is shorter than 50 characters.

This would be my suggestion.
Formulas copied down.

Excel Workbook
ABC
1THIS EXAMPLE NAME IS WAY TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWOTHIS EXAMPLE NAME IS WAY TOO LONG TO FIT INTO50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO
2This is a short nameThis is a short name
Split Text

 Cell Formula B1 =IF(LEN(A1)<51,A1,LEFT(A1,FIND("~",SUBSTITUTE(LEFT(A1,51)," ","~",51-LEN(SUBSTITUTE(LEFT(A1,51)," ",""))))-1)) C1 =TRIM(SUBSTITUTE(A1,B1,""))

<TBODY>
</TBODY>

<TBODY>
</TBODY>

An alternative for B1
=IF(LEN(A1)>50,LEFT(A1,LOOKUP(50,FIND(" ",LEFT(A1,49),ROW(INDIRECT("1:49"))))),A1)

An alternative for B1
=IF(LEN(A1)>50,LEFT(A1,LOOKUP(50,FIND(" ",LEFT(A1,49),ROW(INDIRECT("1:49"))))),A1)
Besides making the formula Volatile (which I am not sure is a positive thing), your proposed function cuts off a word if the space occurs at the 50th character position. For example, given this text in A1...

THIS EXAMPLE'S NAME IS WAY TOOOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO

Your formula eliminates the word "INTO" (highlighted in red) from both B1 and C1

An alternative for B1
=IF(LEN(A1)>50,LEFT(A1,LOOKUP(50,FIND(" ",LEFT(A1,49),ROW(INDIRECT("1:49"))))),A1)

Besides making the formula Volatile (which I am not sure is a positive thing), your proposed function cuts off a word if the space occurs at the 50th character position. For example, given this text in A1...

THIS EXAMPLE'S NAME IS WAY TOOOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO

Your formula eliminates the word "INTO" (highlighted in red) from both B1 and C1
I am WRONG about the eliminating of the word "INTO"... I had an incorrect reference in my testing setup; however, I am not wrong about the your formula being Volatile.

Agreed. Volitility makes my solution inferior.

An alternative for B1
=IF(LEN(A1)>50,LEFT(A1,LOOKUP(50,FIND(" ",LEFT(A1,49),ROW(INDIRECT("1:49"))))),A1)
Apart from the volatility issue already discussed, without a couple of minor adjustments I believe this formula has two other 'issues'. In the screen shot below I have used your formula in rows 1 and 5, mine in rows 9 and 13. The other formulas are the same in each section and are there for reference only.

1. For strings longer than 50, your formula always leaves a space character on the end of the first string (as evidenced by cells B3 and B7). It is possible the OP wants that, but I suspect not.

2. If a space occurs at the 50th or 51st position, the whole word immediately before that space would fit within the 50 characters but your formula moves that word to the second string instead of including it in the first string.

Excel Workbook
ABC
1THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGSTHIS EXAMPLE NAME IS TOO LONG TO FIT INTO50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
2984256
3T CRTO
4
5THIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGSTHIS EXAMPLE NAME IS TOO LONG TO FIT INTO THETEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
6994653
7XT CHE
8
9THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGSTHIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXTCRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
10984948
11T CREXT
12
13THIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGSTHIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE TEXTCRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
14995048
15XT CEXT
Split Text (2)

Last edited:

Replies
4
Views
224
Replies
1
Views
492
Replies
3
Views
1K
Replies
4
Views
334
Replies
5
Views
317

### Forum statistics

1,196,235
Messages
6,014,158
Members
441,807
Latest member
sjkenjalo ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back