MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Alternative Text to cells??


Posted by Gary Hewitt-Long on November 21, 2001 5:36 PM

Hi,

Does anyone know of an easy way to do the following?

I have a list of product descriptions of varying lengths in Column A.

I need to cut the descriptions down to fit into three different columns depending on a preset limit of characters in each column.

i.e.
Column B can only have a maximum of 22 Characters
Column C can only have a maximum of 22 Characters
and
Coumn D can only have a maximum of 14 Characters

The bit I can't quite get is how to make sure that excel goes to the nearest complete word that is <= to the maximum for that column, which I have preset in sheet2!A1:D1

I have tried alternatives of IF, LEN, MID and few more but can't quite seem to get it to work.
I can manage the first one using IF and LEN but it starts getting complicated after that and only being able to nest 7 times is a little restricting :o)

Regards,

Gary Hewitt-Long


Posted by Aladin Akyurek on November 21, 2001 10:54 PM

Care to provide some example prod descriptions (of different lengths) along with expected results in B, C, and D?

Aladin

==========

Posted by Gary Hewitt-Long on November 22, 2001 11:52 AM

Here are some of the product descriptions:

Hinges Butt Steel Self/Colour 40mm (1.1/2")
Hinges Butt Steel Self/Colour 50mm (2")
Hinges Butt Steel Self/Colour 65mm (2.1/2)
Hinges Butt Steel Self/Colour 75mm (3")
Repair Plates Straight Bzp 75mm (3”)
Repair Plates Straight Bzp 100mm (4”)
Corner Brackets Bzp 50mm (2”) Flat
Corner Brackets Bzp 75mm (3”) Flat
Hasp & Staple Wire Japanned 75mm (3")
Hasp & Staple Safety Japanned 75mm (3")
Hasp & Staple Safety Japanned 100mm (4")
Hasp & Staple Wire Japanned 100mm (4")
Corner Braces Bzp 40mm (1.1/2")
Corner Braces Bzp 50mm (2")
Corner Braces Bzp 75mm (3”)
Tower Bolt - Japanned 4" & Screws
Tower Bolt - Japanned 6" & Screws
Tower Bolt - Japanned 8" & Screws
Corner Braces BZP 1" (25mm)
Corner Braces BZP 1.1/2" (40mm)
Corner Braces BZP 2" (50mm)
Corner Braces BZP 3” (75mm)
Cabin Hook Japanned 4" & Screws
Cabin Hook Japanned 6" & Screws
Mortice Knob Set - White & Screws
Mortice Knob Set - Brown & Screws
Mortice Knob Set - Black & Screws
Brass Hat and Coat Hook
Single Robe Hook
Double Robe Hook
4” Cabin Hook
6” Bow Handle
1½” Victorian Door Knob


These would be in column A.

I then need to seperate them into diferent columns

i.e. Column B can have no more than 14 Characters, column C can have no more than 11 characters and column C can have no more than 10 Characters for example (this is the length of text that I may choose for a first description line on a product label).

Therefore the whole of :

4” Cabin Hook
6” Bow Handle

Would fit into column B

However with the descriptions


Mortice Knob Set - White & Screws
Mortice Knob Set - Brown & Screws
Mortice Knob Set - Black & Screws

Mortice Knob would be in Column B, not including the S from set as this is part of a whole word
Column C would have Set - White
and the rest would fit into column D

If this is'nt very self explanotory let me know and I can mail you a smll example in a CSV file or similar.

Regards,

Gary Hewitt-Long

Posted by Aladin Akyurek on November 22, 2001 4:50 PM

Gary --

I'll assume that you have the descriptions in A from A2 on.

In C1 enter: 14
In D1 enter: 11
In E1 enter: 10

In B2 enter: =SUBSTITUTE(TRIM(A2),"/"," ")

In C2 enter: =IF(LEN(B2) <= C$1,B2,reversetext(RIGHT(reversetext(LEFT(B2,C$1)),LEN(reversetext(LEFT(B2,C$1)))-SEARCH(" ",reversetext(LEFT(B2,C$1))))))

In D2 enter: =IF(LEN(B2) <= C$1,"",IF(LEN(B2)<=C$1+D$1,SUBSTITUTE(B2,C2&" ",""),reversetext(RIGHT(reversetext(LEFT(SUBSTITUTE(B2,C2&" ",""),C$1)),LEN(reversetext(LEFT(SUBSTITUTE(B2,C2&" ",""),C$1)))-SEARCH(" ",reversetext(LEFT(SUBSTITUTE(B2,C2&" ",""),C$1)))))))

In E2 enter: =IF(LEN(B2) <= (LEN(C2)+LEN(D2))+1,"",IF(LEN(B2)>C$1+D$1+E$1,reversetext(RIGHT(reversetext(LEFT(SUBSTITUTE(B2,C2&" "&D2&" ",""),C$1)),LEN(reversetext(LEFT(SUBSTITUTE(B2,C2&" "&D2&" ",""),C$1)))-SEARCH(" ",reversetext(LEFT(SUBSTITUTE(B2,C2&" "&D2&" ",""),C$1))))),SUBSTITUTE(B2,C2&" "&D2&" ","")))

Select B2:E2 and copy down as far as needed.

Note 1. The formula in B exchanges "/" for " " before any splitting takes place.
Note 2. You need to add the UDF reversetext to your workbook:

Option Explicit

Function REVERSETEXT(text) As String
'
' Returns its argument, reversed
' J. Walkenbach
'
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
REVERSETEXT = REVERSETEXT & Mid(text, i, 1)
Next i
End Function

Aladin

===========