extraction code from celd

[general]

Hi

I have a Excel problem

MY QUESTION IS:

if A5 = "JhoN FreD SmitH ChonG

then A6 = "JNFDSHCG"

I would like have got solution please !!!


thank you ver
much



[email protected]

 

[Debra Dalgleish]

You could create a User Defined Function:

'===============================
Function GetCode(rng As Range) As String
Dim i As Integer
Dim str As String
i = Len(rng.Value)

For i = 1 To i
str = Mid(rng.Value, i, 1)
If str = " " Then
GetCode = GetCode
Else
If str = UCase(str) Then
GetCode = GetCode & str
Else
GetCode = GetCode
End If
End If
Next i
End Function
'===============================

Then, in cell A6: =GetCode(A5)

 

[Chip Pearson]

I would use a VBA function. For example,

Function UpperChars(InString As String) As String
Dim Ndx As Long
Dim S As String
Dim C As String
For Ndx = 1 To Len(InString)
C = Mid(InString, Ndx, 1)
If C <> " " Then
If StrComp(C, UCase(C), vbBinaryCompare) = 0 Then
S = S & C
End If
End If
Next Ndx
UpperChars = S
End Function


You can then call this from a worksheet cell with a formula like
=UPPERCHARS(A1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

And another version:

Option Explicit
Function JustUpper(myCell As Range) As String

Dim iCtr As Long
Dim myStr As String
Dim myNewStr As String
Dim myChar As String

Set myCell = myCell(1)

myStr = CStr(myCell.Value)
myNewStr = ""
For iCtr = 1 To Len(myStr)
myChar = Mid(myStr, iCtr, 1)
Select Case Asc(myChar)
Case Asc("A") To Asc("Z")
myNewStr = myNewStr & myChar
End Select
Next iCtr

JustUpper = myNewStr

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(one of the differences between Debra's version and this one is the way
non-alphabetic characters are treated.)

 

[general]

1° MUCH


thanks for your attencion

MY QUESTION IS:

if A5 = "JhoN FreD SmitH ChonG

i would like that A6 = "JNFDSHCG"

but,I would like find the solution using functions, no macros
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------

2° MUCH

"Better Word Count in Excel"

=IF(ISBLANK(A1),0,LEN(TRIM(SUBSTITUTE (A1,CHAR(10),
")))-LEN(SUBSTITUTE (SUBSTITUTE(TRIM(A1),CHAR(10),"")," ",""))+1)

I would like work with SPANISH FUNCTIONS

IF=SI, LEN=LARGO
but TRIM= ?????
SUSBSTITUTE=SUSTITUIR OR REEMPLAZAR ?????

THANK VER
: