# Split sentence into rows - No wrapping



## cshetty (Yesterday at 12:31 AM)

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 (Yesterday at 1:23 AM)

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 (Yesterday at 1:28 AM)

Hello,

See if this helps

Book1A1US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY2US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED3FIFTY SEVEN ONLYSheet1Cell FormulasRangeFormulaA2A2=MID(A1,1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+6)A3A3=MID(A1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+8,99)


----------



## cshetty (Yesterday at 1:28 AM)

sinoyon780 said:


> 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 (Yesterday at 1:43 AM)

hrayani said:


> Hello,
> 
> See if this helps
> 
> Book1A1US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY2US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED3FIFTY SEVEN ONLYSheet1Cell FormulasRangeFormulaA2A2=MID(A1,1,FIND("HUNDRED",A1,FIND("THOUSAND",A1,1))+6)A3A3=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 (Yesterday at 1:45 AM)

cshetty said:


> 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 (Yesterday at 2:02 AM)

Try. A1= "your Data" 
In A2

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

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

A1US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLYA2US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSANDA3SEVEN HUNDRED FIFTY SEVEN ONLY


----------



## cshetty (Yesterday at 2:15 AM)

kvsrinivasamurthy said:


> Try. A1= "your Data"
> In A2
> 
> ```
> ...


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 (Yesterday at 2:18 AM)

What is the max number of characters A1 can have.


----------



## cshetty (Yesterday at 2:25 AM)

kvsrinivasamurthy said:


> 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)*


----------



## cshetty (Yesterday at 12:31 AM)

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 (Yesterday at 2:28 AM)

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 (Yesterday at 2:28 AM)

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.xlsmA1US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY2US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND3SEVEN HUNDRED FIFTY SEVEN ONLYSplitCell FormulasRangeFormulaA2A2=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)A3A3=REPLACE(A1,1,LEN(A2)+1,"")


----------



## kvsrinivasamurthy (Yesterday at 3:11 AM)

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


----------



## kvsrinivasamurthy (Yesterday at 3:14 AM)

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


----------



## cshetty (Yesterday at 3:27 AM)

Se


kvsrinivasamurthy said:


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





kvsrinivasamurthy said:


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


2nd 
Tried different fonts as well.


----------



## Peter_SSs (Yesterday at 3:30 AM)

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


----------



## cshetty (Yesterday at 3:34 AM)

Peter_SSs said:


> 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.xlsmA1US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND SEVEN HUNDRED FIFTY SEVEN ONLY2US DOLLARS NINE HUNDRED SIXTY EIGHT THOUSAND3SEVEN HUNDRED FIFTY SEVEN ONLYSplitCell FormulasRangeFormulaA2A2=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)A3A3=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 (Yesterday at 4:11 AM)

cshetty said:


> 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.xlsmA1US DOLLARS NINE HUNDRED2US DOLLARS NINE HUNDRED3 SplitCell FormulasRangeFormulaA2A2=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)A3A3=REPLACE(A1,1,LEN(A2)+1,"")


----------



## cshetty (Yesterday at 4:18 AM)

Peter_SSs said:


> 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.xlsmA1US DOLLARS NINE HUNDRED2US DOLLARS NINE HUNDRED3 SplitCell FormulasRangeFormulaA2A2=IFERROR(LEFT(A1,FIND(" ",A1,40)-1),A1)A3A3=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 (Yesterday at 4:31 AM)

cshetty said:


> 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.


----------



## cshetty (Yesterday at 12:31 AM)

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.


----------



## cshetty (Yesterday at 4:41 AM)

Peter_SSs said:


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


Sorry, my mistake. I just messed up things. Your Solution works as it is.
Thank you, sir.


----------



## Peter_SSs (Yesterday at 5:04 AM)

You're welcome. Glad you got it sorted in the end.


----------

