Split text Column to 30 char and not cut word

[pwebb]

I have textin column B that is 5 to 50 characters long and need to split the text up totwo columns of 30 and not split a word the fun part is that in column B I needto pull out 2 characters to column D and E that are in brackets (AB) first setto D and second to E. Any help will be appreciated <o></o>
<o> </o>
Sample ofthe data I am working with<o></o>

description 1<o></o>	description 2<o></o>	Suffix 1<o></o>	Suffix 2<o></o>
ROLLER<o></o>
INSIDE HORIZONTAL VISE FIXED JAW<o></o>
OUTSIDE HORIZONTAL VISE FIXED JAW<o></o>
OUTSIDE HORIZONTAL VISE MOVEABLE JAW<o></o>
SAW BLADE GUIDE PLATE ( DOOR SIDE )<o></o>
SAW BLADE GUIDE PLATE ( GEAR BOX SIDE )<o></o>
SAW FLANGE ( P.C.D 63)<o></o>
CENTER COLLAR (FOR SAW BLADE )<o></o>
PUMP (ST)(RP)<o></o>
KNURLED SCREW - OUTSIDE HEAT TREAT<o></o>
BAR FL 1.00X3.00X1.13 ALUM 6061-T6511<o></o>
SPR CPRSN (BP)<o></o>
BARLOADER VERT. ROLLER<o></o>

<tbody> </tbody>

<o> </o>

 

[Gerald Higgins]

Hi, welcome to the board.

This should deal with the challenge of breaking the text into two sections, without splitting words.
It doesn't address the point about "pulling out 2 characters to column D and E that are in brackets..." as I don't understand what you mean.
You'll need to clarify what you mean by that if you still want help on that.

Anyway, let's assume your original text string is in the range B1, and you want to split it into a maximum of 2 separate strings, with the first one no more than 30 characters long, and without splitting words.
Let's assume that words are ALWAYS separated by single space characters.
Let's assume that IF the original text string is more than 30 characters long, then there will ALWAYS be a space character to be found in the first 30 characters - if not, you'll have a problem.
Let's assume that IF the original text string is less than or equal to 30 characters long, you don't need to split it at all.
Let's assume that IF the second half of the string turns out to be more than 30 characters long, then that's OK.

We need to have a text character that will not usually be found naturally in your data - I have used "£", but if you might get that character, replace it in my formula with something else.

For the first half
=LEFT(SUBSTITUTE(B1," ","£",30-LEN(SUBSTITUTE(LEFT(B1,30)," ",""))),FIND("£",SUBSTITUTE(B1&"£"," ","£",30-LEN(SUBSTITUTE(LEFT(B1,30)," ",""))))-1)

and for the second half
=IF(LEN(B1)<31,"",MID(SUBSTITUTE(B1," ","£",30-LEN(SUBSTITUTE(LEFT(B1,30)," ",""))),FIND("£",SUBSTITUTE(B1," ","£",30-LEN(SUBSTITUTE(LEFT(B1,30)," ",""))))+1,100))

 

[pwebb]

Thanks forthe response and the warm welcoming to the board<o></o>
<o></o>
Your formulawork for the first part of my problem and thanks<o></o>
<o></o>
What I amtry to do is split the text in column B “1000 of roles” to column C and D whereyour formula work great, the next step is to take any two characters that arein () and place them in the next columns E and F example this is in Column B - PUMP (ST)(RP)<u1></u1> so I would need PUMP in columnC and ST in Column E and RP in column F.

If a macro would do this that is ok too
<o></o>
Thanks againfor your time and help<o></o>

 

[pwebb]

Needs to look like this<o></o>

DATA<o></o>	description 1<o></o>	description 2<o></o>	Suffix 1<o></o>	Suffix 2<o></o>
ROLLER<o></o>	ROLLER<o></o>
INSIDE HORIZONTAL VISE FIXED JAW<o></o>	INSIDE HORIZONTAL VISE FIXED <o></o>	JAW<o></o>
OUTSIDE HORIZONTAL VISE FIXED JAW<o></o>	OUTSIDE HORIZONTAL VISE FIXED <o></o>	JAW<o></o>
OUTSIDE HORIZONTAL VISE MOVEABLE JAW<o></o>	OUTSIDE HORIZONTAL VISE <o></o>	MOVEABLE JAW<o></o>
SAW BLADE GUIDE PLATE ( DOOR SIDE )<o></o>	SAW BLADE GUIDE PLATE <o></o>	( DOOR SIDE )<o></o>
SAW BLADE GUIDE PLATE ( GEAR BOX SIDE )<o></o>	SAW BLADE GUIDE PLATE <o></o>	( GEAR BOX SIDE )<o></o>
SAW FLANGE ( P.C.D 63)<o></o>	SAW FLANGE ( P.C.D 63)<o></o>
CENTER COLLAR (FOR SAW BLADE )<o></o>	CENTER COLLAR (FOR SAW BLADE )<o></o>
PUMP (ST)(RP)<o></o>	PUMP <o></o>		ST<o></o>	RP<o></o>
KNURLED SCREW - OUTSIDE HEAT TREAT<o></o>	KNURLED SCREW - OUTSIDE HEAT <o></o>	TREAT<o></o>
BAR FL 1.00X3.00X1.13 ALUM 6061-T6511<o></o>	BAR FL 1.00X3.00X1.13 ALUM <o></o>	6061-T6511<o></o>
SPR CPRSN (BP)<o></o>	SPR CPRSN<o></o>		BP<o></o>
BARLOADER VERT. ROLLER<o></o>	BARLOADER VERT. ROLLER<o></o>

<tbody> </tbody>

<o> </o>

 

[Rick Rothstein]

For the first half
=LEFT(SUBSTITUTE(B1," ","£",30-LEN(SUBSTITUTE(LEFT(B1,30)," ",""))),FIND("£",SUBSTITUTE(B1&"£"," ","£",30-LEN(SUBSTITUTE(LEFT(B1,30)," ",""))))-1)

Just a note that if the text before the space is exactly 30 characters long, your formula chops off the last word and uses a smaller text string. For example, if this is the data....

The text has to break at the x This goes in next cell

Your formula returns this...

The text has to break at the

but this is exactly 30 characters long, so I think the formula should return it...

The text has to break at the x

 

[Rick Rothstein]

Needs to look like this<o></o>

DATA<o></o>	description 1<o></o>	description 2<o></o>	Suffix 1<o></o>	Suffix 2<o></o>
ROLLER<o></o>	ROLLER<o></o>
INSIDE HORIZONTAL VISE FIXED JAW<o></o>	INSIDE HORIZONTAL VISE FIXED <o></o>	JAW<o></o>
OUTSIDE HORIZONTAL VISE FIXED JAW<o></o>	OUTSIDE HORIZONTAL VISE FIXED <o></o>	JAW<o></o>
OUTSIDE HORIZONTAL VISE MOVEABLE JAW<o></o>	OUTSIDE HORIZONTAL VISE <o></o>	MOVEABLE JAW<o></o>
SAW BLADE GUIDE PLATE ( DOOR SIDE )<o></o>	SAW BLADE GUIDE PLATE <o></o>	( DOOR SIDE )<o></o>
SAW BLADE GUIDE PLATE ( GEAR BOX SIDE )<o></o>	SAW BLADE GUIDE PLATE <o></o>	( GEAR BOX SIDE )<o></o>
SAW FLANGE ( P.C.D 63)<o></o>	SAW FLANGE ( P.C.D 63)<o></o>
CENTER COLLAR (FOR SAW BLADE )<o></o>	CENTER COLLAR (FOR SAW BLADE )<o></o>
PUMP (ST)(RP)<o></o>	PUMP <o></o>		ST<o></o>	RP<o></o>
KNURLED SCREW - OUTSIDE HEAT TREAT<o></o>	KNURLED SCREW - OUTSIDE HEAT <o></o>	TREAT<o></o>
BAR FL 1.00X3.00X1.13 ALUM 6061-T6511<o></o>	BAR FL 1.00X3.00X1.13 ALUM <o></o>	6061-T6511<o></o>
SPR CPRSN (BP)<o></o>	SPR CPRSN<o></o>		BP<o></o>
BARLOADER VERT. ROLLER<o></o>	BARLOADER VERT. ROLLER<o></o>

<TBODY>
</TBODY>

I think you have changed your criteria. You said Gerald's first formula (in Message #2) worked... and it does do what you asked originally (with the one exception that I pointed out in Message #5)... however, his formula does not produce the results you are showing for the two lines I highlighted in red. You seem to be indicating you now want items in parentheses to remain grouped. That, of course raises a question as to how far to take that. For example, if your data was, say, this...

SOME ITEM (HERE IS A LONGISH DESCRIPTION)

it would then be broken up (under your new criteria) like this...

Description1: SOME ITEM
Description2: (HERE IS A LONGISH DESCRIPTION)

So, can you clarify this for us?

Also, when the (ST)(RP) is removed, should the remaining text (without them) be subjected to your 30 character max rule or can the 30 character max rule be applied to the original data (with them in it) and then pull them out afterwards (the easier of the two options to do)?

 

[Rick Rothstein]

I think you still will need to address the questions I raised in Message #6, but let me give you some formulas for the easiest cases to implement and, depending on your answers to the questions in Message #6, I'll try to modify them. So, my assumptions are, first, we ignore the descriptions in parentheses and simply break at the space that yields 30 characters or less for Description1 and, second, we get the 30 character max text before pulling any two-characters-in-parentheses out from that text. With these assumptions, put these formulas in the indicated cells and then copy them down...

C2: =TRIM(LEFT(B2,30-LEN(TRIM(RIGHT(SUBSTITUTE(LEFT(B2&" ",31)," ",REPT(" ",99)),99)))))

D2: =TRIM(SUBSTITUTE(B2,C2,""))

E2: =IF(ISNUMBER(SEARCH(" (??)*",B2)),MID(B2,SEARCH(" (??)*",B2)+1,4),"")

F2: =IF(ISNUMBER(SEARCH(" (??)(??)",B2)),MID(B2,SEARCH(" (??)(??)",B2)+5,4),"")

 

[pwebb]

First let me say thanks for the help.<o></o>
This is the results of Gerald's formula<o></o>

SAW BLADE GUIDE PLATE ( DOOR SIDE )<o></o>	SAW BLADE GUIDE PLATE ( DOOR<o></o>	SIDE )<o></o>
SAW BLADE GUIDE PLATE ( GEAR BOX SIDE )<o></o>	SAW BLADE GUIDE PLATE ( GEAR<o></o>	BOX SIDE )<o></o>

<tbody> </tbody>

<o> </o>
Sorry for the confusion the table in message #4 is how I wouldlike it to look but not a necessity, the ( ) with 2 characters do needs to be moved to Eand F columns.<o></o>
Thanks again for our expertise in this mater <o></o>

 

[Rick Rothstein]

Sorry for the confusion the table in message #4 is how I wouldlike it to look but not a necessity, the ( ) with 2 characters do needs to be moved to Eand F columns.

Okay, I think I have it. Put these formulas in the indicated cells and copy them down...

C2: =TRIM(LEFT(B2,MIN(IF(AND(NOT(ISNUMBER(SEARCH(" (??)*",B2))),LEN(B2)<31),LEN(B2),FIND("(",B2&"(")-1),30-LEN(TRIM(RIGHT(SUBSTITUTE(LEFT(B2&" ",31)," ",REPT(" ",99)),99))))))

D2: =TRIM(SUBSTITUTE(IF(LEN(E2&F2),SUBSTITUTE(B2," "&E2&F2,""),B2),C2,""))

E2: =IF(ISNUMBER(SEARCH(" (??)*",B2)),MID(B2,SEARCH(" (??)*",B2)+1,4),"")

F2: =IF(ISNUMBER(SEARCH(" (??)(??)",B2)),MID(B2,SEARCH(" (??)(??)",B2)+5,4),"")

Note: When the data has two-character suffixes (in parentheses), they are counted in the 30-character max... I think trying to remove them in order to get the 30-character max without them would screw up the other formulas. I'll try looking into removing them from the count, but I am not all that hopeful.

 

[shawnhet]

I have kind of a clunky fix here but I think it should work assuming there are no bracketed characters preceding the two character sets.

Two helper columns with the following in B2 and C2 (data is in A2)

=IF(ISERROR(FIND("(",A2)),SUBSTITUTE(A2," ","*",30-LEN(SUBSTITUTE(LEFT(A2,30)," ",""))),IF(FIND("(",A2)>30,SUBSTITUTE(A2," ","*",30-LEN(SUBSTITUTE(LEFT(A2,30)," ",""))),SUBSTITUTE(A2,"(","*(")))
=SUBSTITUTE(SUBSTITUTE(B2,"*(","**(",1),"**( ","*( ")
and copy down

Then Copy and paste as Values to a new sheet and go to Text to columns and delimit it by "*" - make sure that the treat consecutive delimiters as 1 is unchecked.

I think this should give you the output you are looking for.

Cheers,