I have an excel sheet with strings in the cells in column C. In the
corresponding cell in column D, I want words of the type "TKCDT" or
"TKR25" or "02587" or "25YHT" ie words having capital letters and/or
numbers to be displayed. It does not matter whether this is
accomplished using a fucnction or vba.

Thanks in advance for all the help.

Rajendra

I think you are going to have to provide a little more detail of what you
want to do for us. What kind of string values do you have in column C and
what link is there between those strings and the "words" you want to place
in column D? For example, what in column C dictates that TKCDT is put on
column D? Same question for TKR25 or 02587 etc.

Rick


"Raj" wrote in message
...
I have an excel sheet with strings in the cells in column C. In the
corresponding cell in column D, I want words of the type "TKCDT" or
"TKR25" or "02587" or "25YHT" ie words having capital letters and/or
numbers to be displayed. It does not matter whether this is
accomplished using a fucnction or vba.

Thanks in advance for all the help.

Rajendra

On Nov 29, 9:25 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I think you are going to have to provide a little more detail of what you
want to do for us. What kind of string values do you have in column C and
what link is there between those strings and the "words" you want to place
in column D? For example, what in column C dictates that TKCDT is put on
column D? Same question for TKR25 or 02587 etc.

Rick

"Raj" wrote in message

...



I have an excel sheet with strings in the cells in column C. In the
corresponding cell in column D, I want words of the type "TKCDT" or
"TKR25" or "02587" or "25YHT" ie words having capital letters and/or
numbers to be displayed. It does not matter whether this is
accomplished using a fucnction or vba.

Thanks in advance for all the help.

Rajendra- Hide quoted text -

- Show quoted text -

Thanks Rick.

Cell C2 (and other cells in column C) have sentences like "The TKRTC
value in the 765TW field is not the default.". In cell
D2(corresponding cell in column D), I want TKRTC and 765TW to be
extracted ie words made up of capital letters and/or numbers in the
sentence in cell C2. And repeat the same for all cells in column C.
(Maybe I was wrong in using "string" in place of sentence in my
previous post).

Thanks in advance.

I think this macro will do what you want. Right click the sheet tab where
your data is, select View Code from the popup menu that appears and
copy/paste the macro below into the code window that appears. Run it from
either the VBA editor environment or click Alt+F8 from the sheet where your
data is and run it from there.

Rick

Sub GetCapWords()
Dim I As Long
Dim J As Long
Dim Result As String
Dim Words() As String
Const FirstDataRow = 2
Const DataColumn = 3 'Column C
Const CopyColumn = 4 'Column D
For I = FirstDataRow To Cells(Rows.Count, DataColumn).End(xlUp).Row
Words = Split(Cells(I, DataColumn).Value, " ")
For J = 0 To UBound(Words)
If Words(J) = UCase(Words(J)) Then
Result = Result & ", " & Words(J)
End If
Next
If Len(Result) 0 Then
Result = Mid(Result, 3)
End If
Cells(I, CopyColumn).Value = Result
Result = ""
Next
End Sub



"Raj" wrote in message
...
On Nov 29, 9:25 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I think you are going to have to provide a little more detail of what you
want to do for us. What kind of string values do you have in column C and
what link is there between those strings and the "words" you want to
place
in column D? For example, what in column C dictates that TKCDT is put on
column D? Same question for TKR25 or 02587 etc.

Rick

"Raj" wrote in message

...



I have an excel sheet with strings in the cells in column C. In the
corresponding cell in column D, I want words of the type "TKCDT" or
"TKR25" or "02587" or "25YHT" ie words having capital letters and/or
numbers to be displayed. It does not matter whether this is
accomplished using a fucnction or vba.

Thanks in advance for all the help.

Rajendra- Hide quoted text -

- Show quoted text -

Thanks Rick.

Cell C2 (and other cells in column C) have sentences like "The TKRTC
value in the 765TW field is not the default.". In cell
D2(corresponding cell in column D), I want TKRTC and 765TW to be
extracted ie words made up of capital letters and/or numbers in the
sentence in cell C2. And repeat the same for all cells in column C.
(Maybe I was wrong in using "string" in place of sentence in my
previous post).

Thanks in advance.

On Thu, 29 Nov 2007 07:47:27 -0800 (PST), Raj wrote:

I have an excel sheet with strings in the cells in column C. In the
corresponding cell in column D, I want words of the type "TKCDT" or
"TKR25" or "02587" or "25YHT" ie words having capital letters and/or
numbers to be displayed. It does not matter whether this is
accomplished using a fucnction or vba.

Thanks in advance for all the help.

Rajendra

Here is a UDF that will return the words specified, separated by a <space. If
you want a different separator, or none, the UDF can be modified to allow that.

To enter it, <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 use this, enter the formula =reSpecial(cell_ref) in the cell in column D
where you want the results. cell_ref is the corresponding cell in column C.

====================================
Option Explicit
Function reSpecial(str As String) As String
Dim re As Object, mc As Object
Dim I As Long
Const sPat As String = "\b[A-Z0-9]+\b"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
re.ignorecase = False

If re.test(str) = True Then
Set mc = re.Execute(str)
For I = 0 To mc.Count - 1
reSpecial = reSpecial & mc(I) & " " '<space as separator
Next I
reSpecial = Trim(reSpecial)
End If
End Function
=======================================
--ron

"Ron Rosenfeld" wrote in message
...
On Thu, 29 Nov 2007 07:47:27 -0800 (PST), Raj wrote:

I have an excel sheet with strings in the cells in column C. In the
corresponding cell in column D, I want words of the type "TKCDT" or
"TKR25" or "02587" or "25YHT" ie words having capital letters and/or
numbers to be displayed. It does not matter whether this is
accomplished using a fucnction or vba.

Thanks in advance for all the help.

Rajendra

Here is a UDF that will return the words specified, separated by a
<space. If
you want a different separator, or none, the UDF can be modified to allow
that.

To enter it, <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 use this, enter the formula =reSpecial(cell_ref) in the cell in column
D
where you want the results. cell_ref is the corresponding cell in column
C.

====================================
Option Explicit
Function reSpecial(str As String) As String
Dim re As Object, mc As Object
Dim I As Long
Const sPat As String = "\b[A-Z0-9]+\b"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
re.ignorecase = False

If re.test(str) = True Then
Set mc = re.Execute(str)
For I = 0 To mc.Count - 1
reSpecial = reSpecial & mc(I) & " " '<space as separator
Next I
reSpecial = Trim(reSpecial)
End If
End Function
=======================================
--ron

For those who might be interested in a non-RegEx solution, here is my
offering for a UDF (also using a space delimiter between found words)...

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Words = Split(R, " ")
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & " " & Words(X)
End If
Next
If Len(GetCapWords) 0 Then
GetCapWords = Mid(GetCapWords, 3)
End If
End Function

What surprises me a little, Ron, is that it looks shorter than the RegEx
solution... not what I would have expected.

Rick

On Thu, 29 Nov 2007 15:39:15 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

What surprises me a little, Ron, is that it looks shorter than the RegEx
solution... not what I would have expected.

Well, here's a shorter, possibly better optimized version:

===============================
Function reSpecial(str As String) As String
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\b[A-Z0-9]+\b"
Set mc = re.Execute(str)
For Each m In mc
reSpecial = reSpecial & m & " "
Next m
reSpecial = Trim(reSpecial)
End Function
======================================

But I think the big advantage is in development time. If the OP wants to
change a parameter, it's probably just going to involve changing the regex.
--ron

On Thu, 29 Nov 2007 15:39:15 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Words = Split(R, " ")
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & " " & Words(X)
End If
Next
If Len(GetCapWords) 0 Then
GetCapWords = Mid(GetCapWords, 3)
End If
End Function

What surprises me a little, Ron, is that it looks shorter than the RegEx
solution... not what I would have expected.

Rick,

Given this string:

The TKRTC value in the 765TW field is not the default

Your routine, on my machine, returns:
KRTC 765TW
(The initial T is missing)

Also, given a multiline string:

The TKRTC
value in the 765TW field is not the default

(with no space after TKRTC), your routine returns:
65TW

missing TKRTC and also again missing the first character.
--ron

As for the missing first letter... that's what happens when you rush. My
brother is due any minute for dinner, so I had to get ready... I made a last
minute change from my original delimiter of a comma-space to just the space
that you used and I forgot to adjust the code for the length difference. I
change my code to include a Delimiter constant (set to equal a space; but
changeable to any combination of characters) and put code in to account for
it. As for the multi-line problem... I never even gave it a thought
originally. Below is a revised function that handles both problems (thanks
for catching them)...

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Const Delimiter = " "
Words = Split(Replace(Replace(R.Value, vbLf, " "), vbCr, " "))
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & Delimiter & Words(X)
End If
Next
If Len(GetCapWords) 0 Then
GetCapWords = Mid(GetCapWords, 1 + Len(Delimiter))
End If
End Function

Rick


"Ron Rosenfeld" wrote in message
...
On Thu, 29 Nov 2007 15:39:15 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Words = Split(R, " ")
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & " " & Words(X)
End If
Next
If Len(GetCapWords) 0 Then
GetCapWords = Mid(GetCapWords, 3)
End If
End Function

What surprises me a little, Ron, is that it looks shorter than the RegEx
solution... not what I would have expected.

Rick,

Given this string:

The TKRTC value in the 765TW field is not the default

Your routine, on my machine, returns:
KRTC 765TW
(The initial T is missing)

Also, given a multiline string:

The TKRTC
value in the 765TW field is not the default

(with no space after TKRTC), your routine returns:
65TW

missing TKRTC and also again missing the first character.
--ron

On Thu, 29 Nov 2007 17:39:14 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

As for the missing first letter... that's what happens when you rush. My
brother is due any minute for dinner, so I had to get ready... I made a last
minute change from my original delimiter of a comma-space to just the space
that you used and I forgot to adjust the code for the length difference. I
change my code to include a Delimiter constant (set to equal a space; but
changeable to any combination of characters) and put code in to account for
it. As for the multi-line problem... I never even gave it a thought
originally. Below is a revised function that handles both problems (thanks
for catching them)...

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Const Delimiter = " "
Words = Split(Replace(Replace(R.Value, vbLf, " "), vbCr, " "))
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & Delimiter & Words(X)
End If
Next
If Len(GetCapWords) 0 Then
GetCapWords = Mid(GetCapWords, 1 + Len(Delimiter))
End If
End Function

Rick

You're getting there. Still a problem with returning extra spaces -- if there
are multiple spaces after the CapWord, your routine returns them, also.

It seems that what is happening is that if there are multiple, sequential
spaces, the Split function returns a null string ("") for the spaces after the
first. Since "" = Ucase(""), the null string gets concatenated, followed by
your Delimiter.

Enjoy your dinner! We just had a wonderful one. My mother-in-law and niece
are visiting from the Azores, and they go home tomorrow. My wife and niece
made a great "going away" meal, and some of our kids were over, too.
--ron