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.

 

[sinoyon780]

The formula you can use would be:

=CONCATENATE(A1, CHAR(10), B1)

Where A1 is the cell containing "US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED" and B1 is the cell containing "FIFTY SEVEN ONLY". The CHAR(10) function will insert a line break between the two cells, causing the second part to be printed on a new line on your pre-printed pay-order stationery.

 

[hrayani]

Hello,

See if this helps

Book1
	A
1	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY
2	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED
3	FIFTY SEVEN ONLY
Sheet1

Cell Formulas
Range	Formula
A2	A2	=MID(A1,1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+6)
A3	A3	=MID(A1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+8,99)

 

[cshetty]

The formula you can use would be:

=CONCATENATE(A1, CHAR(10), B1)

Where A1 is the cell containing "US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED" and B1 is the cell containing "FIFTY SEVEN ONLY". The CHAR(10) function will insert a line break between the two cells, causing the second part to be printed on a new line on your pre-printed pay-order stationery.

Thanks for the reply.

This doesnt serve the purpose dear.

Let me explain in detail.
in A1 cell the text is - US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY

Now i need to split this text between two cells. B1 & B2

But B1 and B2 has a fixed width which matches with the pre-printed form. SO in the above case i want US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED in B1 and remaining text in B2. (This may change as the text changes). This is basically depend on the width of B1 and B2.

 

[cshetty]

Hello,

See if this helps

Book1
	A
1	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY
2	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED
3	FIFTY SEVEN ONLY
Sheet1

Cell Formulas
Range	Formula
A2	A2	=MID(A1,1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+6)
A3	A3	=MID(A1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+8,99)

Thank you for the reply.

Cell A1 text keeps changing, which need to be split between A2 and A3 which has fixed column width.
Please have a look at the image for better understanding.
Thank you in advance.

 

[hrayani]

Thank you for the reply.

Cell A1 text keeps changing, which need to be split between A2 and A3 which has fixed column width.
Please have a look at the image for better understanding.
Thank you in advance.

What is the column width then ??

 

[kvsrinivasamurthy]

Try. A1= "your Data"
In A2

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

In A3

=SUBSTITUTE(A1,A2&" ","")

A1	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY
A2	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND
A3	SEVEN HUNDRED FIFTY SEVEN ONLY

 

[cshetty]

Try. A1= "your Data"
In A2

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

In A3

=SUBSTITUTE(A1,A2&" ","")

A1	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY
A2	US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND
A3	SEVEN HUNDRED FIFTY SEVEN ONLY

Thanks for the reply.

So, in this case the whole text in A1 is split into almost half. (Hope I understood correctly)
What if A1 has a shorter length?
For example, "US Dollars One Million Only" (The formula splits this as well, though the whole length can fit in A2 leaving A3 blank.

 

[kvsrinivasamurthy]

What is the max number of characters A1 can have.

 

[cshetty]

What is the max number of characters A1 can have.

Sir, this is one more issue.

If I type all A in A1, it can accommodate 31 As
C=34, L=46 and I =69

Width of the cell is 39.57(282 Pixels)