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

[mesje]

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

 

[davesweep]

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 )

 

[mesje]

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

 

[Peter_SSs]

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
	A	B	C
1	THIS EXAMPLE NAME IS WAY TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO	THIS EXAMPLE NAME IS WAY TOO LONG TO FIT INTO	50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO
2	This is a short name	This is a short name
Split Text

 

[West Man]

Spreadsheet Formulas

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)

 

[Rick Rothstein]

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

 

[Rick Rothstein]

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.

 

[West Man]

Agreed. Volitility makes my solution inferior.

 

[Peter_SSs]

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
	A	B	C
1	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO	50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
2	98	42	56
3	T CR	TO
4
5	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE	TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
6	99	46	53
7	XT C	HE
8
9	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT	CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
10	98	49	48
11	T CR	EXT
12
13	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS	THIS EXAMPLE NAME IS TOO LONG TO FIT INTO THE TEXT	CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
14	99	50	48
15	XT C	EXT
Split Text (2)