Please help. Excel formula to split cell value to 2 cells

[cedric_dranreb]

Hi all,

Please help me on this:

I want to have a formula in cell “B10” that checks the number of characters in cell “A1” that contain say for example the text string “One Thousand Five Hundred Twenty Five”. When the number of characters exceeds the maximum of 20, it will trim the result to 20 characters or LESS but will still preserve the whole text prior to the maximum character limit. In this case, 20 characters will result to “One Thousand Five Hu”. With the given condition, the formula in cell “B10” must result to “One Thousand Five” thereby still preserving the complete words prior to the maximum limit.

In cell “B11”, another formula must also result in “Hundred Twenty Five” completing the whole text string in cell “A1”. I just can’t figure out what the formulas must be to have the desired results.

Any help is very much appreciated. Thank you.

 

[bschwartz]

Are there ever text strings with more than 40 characters? If so, should the remainder be in yet a 3rd cell, and a 4th cell, etc?

If there are only text strings with 40 characters or less, then maybe this will work (from Rick Rothstein's playbook):

Excel 2010
	A	B	C
1	One Thousand Five Hundred Twenty Five	One Thousand Five	Hundred Twenty Five
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(LEFT(A1,20),SEARCH(TRIM(LEFT(RIGHT(SUBSTITUTE(" "&LEFT(A1,20)," ",REPT(" ",99)),99),99)),LEFT(A1,20))-2)
C1	=RIGHT(A1,LEN(A1)-LEN(B1))

 

[bschwartz]

Source:

Get "Reversed" Field from Delimited Text String

 

[JoeMo]

Hi all,

Please help me on this:

I want to have a formula in cell “B10” that checks the number of characters in cell “A1” that contain say for example the text string “One Thousand Five Hundred Twenty Five”. When the number of characters exceeds the maximum of 20, it will trim the result to 20 characters or LESS but will still preserve the whole text prior to the maximum character limit. In this case, 20 characters will result to “One Thousand Five Hu”. With the given condition, the formula in cell “B10” must result to “One Thousand Five” thereby still preserving the complete words prior to the maximum limit.

In cell “B11”, another formula must also result in “Hundred Twenty Five” completing the whole text string in cell “A1”. I just can’t figure out what the formulas must be to have the desired results.

Any help is very much appreciated. Thank you.

The two bold statements in your post seem to be contradictory so I'm unsure if this is what you want.

Excel Workbook
	A	B
1	One Thousand Five Hundred Twenty Five
2
3
4
5
6
7
8
9
10		One Thousand Five Hu
11		ndred Twenty Five
Sheet14

 

[bschwartz]

Slight Correction:

Excel 2010
	A	B	C
1	One Thousand Five Hundred Twenty Five	One Thousand Five	Hundred Twenty Five
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(LEFT(A1,20),SEARCH(TRIM(LEFT(RIGHT(SUBSTITUTE(" "&LEFT(A1,20)," ",REPT(" ",99)),99),99)),LEFT(A1,20))-2)
C1	=RIGHT(A1,LEN(A1)-LEN(B1)-1)