excel formula to split cell value in two cells

hi all.

please help me on this.

I want to have a formula in cell “A3” 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 “A3” must result to “One Thousand Five” thereby still preserving the complete words prior to the maximum limit.

In cell “A4”, 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 in CAPITALIZED CHARACTERS.
Formulas previously provided by helpful forum members are as follows:

A3
=iferror(TRIM(LEFT(A1,FIND("@@",SUBSTITUTE(A1," ","@@",20-LEN(SUBSTITUTE(LEFT(A1,20)," ","")))))),"")

A4
=iferror(TRIM(SUBSTITUTE(A1,A3,"")),"")

Any help is very much appreciated. Thank you.


cedric

A3 =upper(iferror(trim(left(a1,find("@@",substitute(a1," ","@@",20-len(substitute(left(a1,20)," ","")))))),""))
a4 =iferror(trim(substitute(upper(a1),a3,"")),"")

thanks a lot DAVID. your solution works the way i want it.

Originally Posted by cedric_dranreb

thanks a lot DAVID. your solution works the way i want it.

Thanks for the feedback. It's always good to hear that a suggestion works.

Please click the *Add Reputation button at bottom left, and mark the thread as SOLVED by clicking Thread Tools above your first post, select "Mark your thread as Solved".