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
===========
