Split sentence into rows - No wrapping

[cshetty]

I have a tricky requirement. (Tricky for me )

My sentence in excel cell is "US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY"

I need to print this on a pre printed pay-order stationery. The width of the line is limited on the stationery. I can print up to US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED on the first line and FIFTY SEVEN ONLY should go to the next line on the stationery.

I tried with wrapping the text in excel cell, but one of sentence prints on the line of the stationery.
Is there a way to split the line using a formula so that i can distribute the line into two rows.

Hope i have explained my query properly.

 

[kvsrinivasamurthy]

In A2

=IF( LEN(A1)>55,LEFT(A1,FIND(" ",A1,INT(LEN(A1)/2))-1),A1)

In A3

=IF( LEN(A1)>55,SUBSTITUTE(A1,A2&" ",""),"")

 

[Peter_SSs]

Try this. Experiment with changing the "40" in the formula until you have a number that roughly suits your required column width.

23 01 12.xlsm
	A
1	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY
2	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND
3	SEVEN HUNDRED FIFTY SEVEN ONLY
Split

Cell Formulas
Range	Formula
A2	A2	=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)
A3	A3	=REPLACE(A1,1,LEN(A2)+1,"")

 

[kvsrinivasamurthy]

Pl see Post#11.
If it is not suiting Change 55 to 45 or 31.

 

[kvsrinivasamurthy]

One more Clarification.
Which line(1st or 2nd) over prints on stationary when Wordrap is done.

 

[cshetty]

Se

One more Clarification.
Which line(1st or 2nd) over prints on stationary when Wordrap is

One more Clarification.
Which line(1st or 2nd) over prints on stationary when Wordrap is done.

2nd
Tried different fonts as well.

 

[Peter_SSs]

So, have you tried any of the recent formulas suggested?

 

[cshetty]

Try this. Experiment with changing the "40" in the formula until you have a number that roughly suits your required column width.

23 01 12.xlsm
	A
1	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY
2	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND
3	SEVEN HUNDRED FIFTY SEVEN ONLY
Split

Cell Formulas
Range	Formula
A2	A2	=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)
A3	A3	=REPLACE(A1,1,LEN(A2)+1,"")

Mr. Peter

I guess your solution going to work for me. Let me try some samples before ticking Solution mark

Made a small change to formula incase if i have a shorter length. than 45.

=LEFT (A1, FIND (" 1, MIN(LEN(A1),45))-1)

 

[Peter_SSs]

Made a small change to formula incase if i have a shorter length. than 45.

=LEFT (A1, FIND (" 1, MIN(LEN(A1),45))-1)

That isn't a valid formula.
In any case, my suggested formula for A2 (& A3) already handles the case if the A1 text is short. Did you try it with short text?

23 01 12.xlsm
	A
1	US DOLLARS NINE HUNDRED
2	US DOLLARS NINE HUNDRED
3
Split

Cell Formulas
Range	Formula
A2	A2	=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)
A3	A3	=REPLACE(A1,1,LEN(A2)+1,"")

 

[cshetty]

That isn't a valid formula.
In any case, my suggested formula for A2 (& A3) already handles the case if the A1 text is short. Did you try it with short text?

23 01 12.xlsm
	A
1	US DOLLARS NINE HUNDRED
2	US DOLLARS NINE HUNDRED
3
Split

Cell Formulas
Range	Formula
A2	A2	=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)
A3	A3	=REPLACE(A1,1,LEN(A2)+1,"")

Oh Ya..

Only thing is I have to make sure there is a space at the end of the main sentence in A1. Thats not an issue.

 

[Peter_SSs]

Only thing is I have to make sure there is a space at the end of the main sentence in A1.

Why would you have to do that? My examples don't have a space at the end.