LEFT and RIGHT full words

[Hipsway]

I have a catalogue which I need to upload into a purchase orderin
system. However field length for description is limited to 4 lines o
40 characters. All of the descriptions are less than 80 characters s
need only be split into two.

The problem I am encountering is that once I use the Left function t
take the first 40 characters and the RIGHT funtion in conjuction wit
LEN for the remainder the split often occurs part way through words.

Is it possible to use these functions to also find the nearest space t
use as the split point?

The two formulae used are below;

LEFT(B1,40)
and
=IF(C1<40,"",RIGHT(B1,(C1-40))) "where C1 is LEN(B1)"

I would appreciate any assistance

Ke

 

[Guest]

Hi
try MID instead:
IF(LEN(A1)>40,MID(A1,41,1024),"")

 

[Myrna Larson]

Hi, Frank:

I don't see how that deals with the problem of not breaking the text in the
middle of a word.

Myrna Larson

 

[Frank Kabel]

Hi Myrna
uups, it doesn't as I overread this requirement. So good point. Adapted
solution:

Left part:
=LEFT(A1,LOOKUP(2,1/(MID(LEFT(A1,40),seq,1)=" "),seq)-1)

Right part:
=MID(A1,LOOKUP(2,1/(MID(LEFT(A1,40),seq,1)=" "),seq)+1,1024)

where seq is a defined name referring to
seq: =ROW(INDIRECT("1:1024"))

If you also want to take care of the case that there's no blank before
the 40th character change the formulas to:
Left part:
=IF(ISERROR(LOOKUP(2,1/(MID(LEFT(A1,40),seq,1)="
"),seq)),LEFT(A1,40),LEFT(A1,LOOKUP(2,1/(MID(LEFT(A1,40),seq,1)="
"),seq)-1))

Right part:
=IF(ISERROR(LOOKUP(2,1/(MID(LEFT(A1,40),seq,1)="
"),seq)),MID(A1,41,1024),MID(A1,LOOKUP(2,1/(MID(LEFT(A1,40),seq,1)="
"),seq)+1,1024))

 

[Myrna Larson]

If you want the split lines to be displayed one below the other, you can get
Excel to do it for you, without formulas.

1. Change the font to a fixed width like Courier
2. In an empty cell in the column, type the formula
=REPT("X",40)
3. Manually adjust the column width so those 40 characters just fit
4. Go to your description, select that cell and the (empty) cell
below it, then Edit/Fill and select the Justify option.

Assuming you have the blank lines in place, once you've justified the 1st pair
of lines, you can select the next 2, then press F4 to repeat the action, so it
should go fairly quickly. But it could still be tedious if there are a lot of
rows.

If you want to do this with formulas, I would use a helper column such as C
that contains this array formula:

=IF(LEN(A1)>40,MAX((MID(A1,ROW($1:$41),1)=" ")*ROW($1:$41)),LEN(A1))

Since it's an array formula, you must enter it with CTRL+SHIFT+ENTER.

To get the first line: =LEFT(A1,C1-1)
For the 2nd: =MID(A1,C1+1,255)

BTW, this won't work if there are no spaces in the 1st 41 characters.

Or you can use this VBA function which returns an array.

Function SplitLines(sText As String, MaxLen As Long) As Variant
Dim i As Long
Dim j As Long
For i = MaxLen + 1 To 1 Step -1
If Mid$(sText, i, 1) = " " Then
Exit For
End If
Next i
If i = 0 Then i = MaxLen + 1
SplitLines = Application.Transpose( _
Array(Left$(sText, i - 1), Mid$(sText, i + 1)))
End Function

To use this UDF in a formula, let's say the original text is in B1, and the
split text is to go into C1 and C2.

Select both C1 and C2, enter the formula =SplitLines(B1,40) and press
CTRL+SHIFT+ENTER.

If you want the text in, say, C1 and D1, you can modify the function: change
the last line to

SplitLines = Array(Left$(sText, i - 1), Mid$(sText, i + 1))

or modify the formula to

=TRANSPOSE(SplitLines(B1,40))