Need to split a string based on space and character limit

[Eswingler]

I am trying to take the following

The quick brown fox jumped over the sleeping lazy yellow dog

and break it into

The quick brown fox jumped
and
over the sleeping lazy yellow dog

This is based on the space before the 30th character.

Appreciate any help

Eric

 

[Michael M]

Is it always going to be the 30th character ?
IS there other text you have to extract ?
AND
Your example is actually the 26th Character

=LEFT(A1,26)

 

[pgc01]

Hi Eric
Welcome to the board

To split the string by the last space before the 30th character, try in B1:

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",30-LEN(SUBSTITUTE(LEFT(A1,30)," ",""))))-1)
Copy down


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">The quick brown fox jumped over the sleeping lazy yellow dog</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">The quick brown fox jumped</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">This is based on the space before the 30th character</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">This is based on the space</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Need to split a string based on space and character limit</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Need to split a string based</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">I am trying to take the following</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">I am trying to take the</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[Eswingler]

That is awesome! Thank you so much. Would I use the same to get the remainder in C1 with =right(

 

[pgc01]

That is awesome! Thank you so much. Would I use the same to get the remainder in C1 with =right(

You could use the same logic, but since you already have the result in B1 you can use it to say that C1 is equal to A1 - B1

In C1:

=RIGHT(A1,LEN(A1)-LEN(B1)-1)

 

[Eswingler]

Thank you very much Michael. You have saved me a boat load of work. I appreciate your input and help.

Eric

 

[ziqbal]

Hi, PGC, i was looking the formula for splitting 30 or less characters without splitting the words and i found it is amazing and working 100% but i am unable to understand specially "|", please explain in detail.

Thanks,

Zahid

 

[pgc01]

Hi Zahid
Welcome to the board

The logic is:

a) find the first space before the 30th character (from the right)
b) get the left part of the string up to that point

The "|" has not special meaning, you can use instead a "#" or a "@", for example. Just make sure you use a character that is not used in your source text.

To execute a) what I did was to replace the first space before the 30th character by a "|". You then just have to find that character to know how many characters you have to extract,

Try:

=SUBSTITUTE(A1," ","|",30-LEN(SUBSTITUTE(LEFT(A1,30)," ","")))

on some examples strings to see how it replaces the first space before the 30th character by a "|".

 

[ziqbal]

Hi, PGC many many thanks for explaining this beautiful formula for splitting. 04 years before i got the following long Array formula for splitting the text into 30 or less characters without breaking the words but your current formula just awesome.

TRIM(IF(LEN(A1)<=30,A1,LEFT(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")* (ROW(INDIRECT("1:"&LEN(A1)))<=30)*(ROW(INDIRECT("1:"&LEN(A1)))),1))))

If possible if you can explain in detail the above formula for betting understanding excel formulas.

Thanks for your kind support in this regard.

Zahid