Can I do this in EXCEL? - removing text from multiple rows so only specified codes remain

[bob green]

I have data in the following format in a spreadsheet :

$GROUP social puts in $FEEL-GOOD better mood $DISTRESS gets in good
mood

$AVAILABLE day off $RELAX relaxing and $PHYSICAL FUNCTION sleep better
and $HABIT part of regular routine.


I want to be able to remove the text so only the codes remain, e.g.,

$GROUP $FEEL-GOOD $DISTRESS
$AVAILABLE $RELAX $PHYSICAL FUNCTION $HABIT



Can I do this using EXCEL?

Any assistance is appreciated,

regards

Bob Green

 

[Peo Sjoblom]

You would be better off doing this in MS Word and since the majority of
Excel users
has Office maybe you have that too? Copy over the data to word as text, do
edit>replace, in the find what put

<[a-z]{2,20}>

select use wildcards and leave the what box empty

if there are words with more that 20 letters change the 20 within the
parenthesis to
cover that

 

[Peo Sjoblom]

Sorry, should have been "leave the replace with box empty"

--

Regards,

Peo Sjoblom

You would be better off doing this in MS Word and since the majority of
Excel users
has Office maybe you have that too? Copy over the data to word as text, do
edit>replace, in the find what put

<[a-z]{2,20}>

select use wildcards and leave the what box empty

if there are words with more that 20 letters change the 20 within the
parenthesis to
cover that

--

Regards,

Peo Sjoblom

I have data in the following format in a spreadsheet :

$GROUP social puts in $FEEL-GOOD better mood $DISTRESS gets in good
mood

$AVAILABLE day off $RELAX relaxing and $PHYSICAL FUNCTION sleep better
and $HABIT part of regular routine.


I want to be able to remove the text so only the codes remain, e.g.,

$GROUP $FEEL-GOOD $DISTRESS
$AVAILABLE $RELAX $PHYSICAL FUNCTION $HABIT



Can I do this using EXCEL?

Any assistance is appreciated,

regards

Bob Green

 

[Harlan Grove]

You would be better off doing this in MS Word and since the majority of
Excel users has Office maybe you have that too? Copy over the data to
word as text, do edit>replace, in the find what put

<[a-z]{2,20}>

select use wildcards and leave the what box empty

if there are words with more that 20 letters change the 20 within the
parenthesis to cover that

....

Suboptimal. You're assuming the OP's examples are fully representative,
i.e., there'll never be capitals in the plain text, nor punctuation, digits,
etc.

Maybe Word's watered-down regular expressions (called wildcards) can handle
more general situations, but I doubt it.

As an alternative, since most Office users have Windows Script Host
installed (whether they know it or not), wrap a udf around the VBScript
regular expression object. See

http://groups.google.com/[email protected]

The Subst function therein could be used as

=subst(A5,".*?(\$[A-Z][- A-Z]*[A-Z]?)[^$]*","$1 ")

which retains only substrings beginning with $ immediately followed by a
capital letter and followed by capital letters and embedded spaces and
dashes and ending with a capital letter.

Right tool for the job. IMO, that ain't Word.