Re : Excel Wrap-Text with Relatively Loose Strings

1. Enters an Excel worksheet (received from the wilderness).

2. In a fixed-width cell, there is an entry such as the following
string :-

ZSL - Postion
Indicator
HS - Hand switch / Push
Button
SS - Selector
switch
DCS - Distributed Control System SDV
- Shut down
Valve BDV -
Blow down Valve
LCP - Local control
Panel BSY -
Solenoid Valve

3. It was after discreetly manipulating the string (by adding
extraneous spaces between the given items, as shown), it rendered the
string to assemble as follows (See ?!) :-

ZSL - Postion Indicator
HS - Hand switch / Push Button
SS - Selector switch
DCS - Distributed Control System
SDV - Shut down Valve
BDV - Blow down Valve
LCP - Local control Panel
BSY - Solenoid Valve

3. Obviously, legibility of the string is at the mercy of the Cell-
width ; subsequent changes of the Cell-width will render the string
gibberish ...... and there, another repeated attempt to edit the
string by adding or deleting the superfluous spaces here and there to
rectify the situation.

4. One is indeed hapless to contemplate a correction by inserting Alt-
Enter between the given items and eliminating the spacing strings in-
situ.

5. Is there not an applicable formula to neutralize the exemplary
loose string such that the subsequent normalization of the string
would be an orderly assembly as shown above ?

6. Note that embedded in that item, Hand switch / Push Button, there
are 2 spaces located on both sides of the slash(/). And there, the
length of spacing strings would be 3 spaces and beyond.

7. Please share your experiences and recommend a solution.

8. Regards.

On Mon, 12 May 2008 02:50:00 -0700 (PDT), TKT-Tang <tkt_tang@hotmail.com>
wrote:

>Re : Excel Wrap-Text with Relatively Loose Strings
>
>1. Enters an Excel worksheet (received from the wilderness).
>
>2. In a fixed-width cell, there is an entry such as the following
>string :-
>
>ZSL - Postion
>Indicator
>HS - Hand switch / Push
>Button
>SS - Selector
>switch
>DCS - Distributed Control System SDV
>- Shut down
>Valve BDV -
>Blow down Valve
>LCP - Local control
>Panel BSY -
>Solenoid Valve
>
>3. It was after discreetly manipulating the string (by adding
>extraneous spaces between the given items, as shown), it rendered the
>string to assemble as follows (See ?!) :-
>
>ZSL - Postion Indicator
>HS - Hand switch / Push Button
>SS - Selector switch
>DCS - Distributed Control System
>SDV - Shut down Valve
>BDV - Blow down Valve
>LCP - Local control Panel
>BSY - Solenoid Valve
>
>3. Obviously, legibility of the string is at the mercy of the Cell-
>width ; subsequent changes of the Cell-width will render the string
>gibberish ...... and there, another repeated attempt to edit the
>string by adding or deleting the superfluous spaces here and there to
>rectify the situation.
>
>4. One is indeed hapless to contemplate a correction by inserting Alt-
>Enter between the given items and eliminating the spacing strings in-
>situ.
>
>5. Is there not an applicable formula to neutralize the exemplary
>loose string such that the subsequent normalization of the string
>would be an orderly assembly as shown above ?
>
>6. Note that embedded in that item, Hand switch / Push Button, there
>are 2 spaces located on both sides of the slash(/). And there, the
>length of spacing strings would be 3 spaces and beyond.
>
>7. Please share your experiences and recommend a solution.
>
>8. Regards.

Fairly simple to do using regular expressions and a VBA Macro, given your data
format as presented.

1. Replace the existing white-space characters (spaces and line-feeds) with a
single space.

2. For every sequence of <space> followed by two or more capital letters, and
followed by another <space>, replace the initial <space> with a LF. This
format is critical, as it is the only thing in your data that seems to
differentiate the beginning of a new line.

3. (Not included in this macro, since more information would be needed), size
appropriately the rowheight and columnwidth.

Note: In this macro, the "wrapped" text is written into the adjacent cell. It
could just as easily replace the existing cell, but things are easier to debug
this way. You should be able to modify it to your needs.

Note2: I'm not sure, from your specifications, just what you want to do with
any extra <space>'s surrounding the slash. In this example, I trimmed this
also to a single space on either side. But this is easily modified.

In any event, put the code below into a regular module; select your cell(s)
with the data; and RUN the macro.

To enter the macro, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To RUN the macro, first select your cell(s) with the data to be corrected.
<alt-F8> opens the macro dialog box. Select the Wrap macro, then RUN.


====================================
Option Explicit
Sub Wrap()
Dim c As Range
Dim re As Object
Dim str As String

Const sPat1 As String = "(\s+)"
Const sRep1 As String = " "
Const sPat2 As String = "\s([A-Z]{2,}\s)"
Const sRep2 As String = vbLf & "$1"

Set re = CreateObject("vbscript.regexp")
With re
.ignorecase = False
.Global = True
End With

For Each c In Selection
str = c.Value
re.Pattern = sPat1
If re.test(str) = True Then
'Replace all line feeds and extraneous spaces
'with a single space
str = re.Replace(str, sRep1)
'Add LF before each sequence of 2+ capital letters
'except the very first
re.Pattern = sPat2
str = re.Replace(str, sRep2)
c.Offset(0, 1).Value = str
End If
Next c
End Sub
======================================
--ron

Mr. Ron Rosenfeld,

Thank you for responding to the query case.

Will study your recommendation.

Wish to have a formula applied adjacent to the affected cell(s) such
that,
Copy > Paste Special will then overwrite the extraneous strings in the
affected cell(s).

As for, 'Add LF before each sequence of 2+ capital letters ......
the distinguishing feature being the lengthy spacing strings which
will be replaced by LF's respectively.

Thank you once again.

On Mon, 12 May 2008 16:52:43 -0700 (PDT), TKT-Tang <tkt_tang@hotmail.com>
wrote:

>Mr. Ron Rosenfeld,
>
>Thank you for responding to the query case.
>
>Will study your recommendation.
>
>Wish to have a formula applied adjacent to the affected cell(s) such
>that,
>Copy > Paste Special will then overwrite the extraneous strings in the
>affected cell(s).
>
>As for, 'Add LF before each sequence of 2+ capital letters ......
>the distinguishing feature being the lengthy spacing strings which
>will be replaced by LF's respectively.
>
>Thank you once again.

You're welcome.

Post back after you have had a chance to try this out. I did not use a
formula, but could if necessary.
--ron