0

How to extract cap letter from cell to another cell

John Doe = JD

SomeWord = SW

ExTraCt = ETC

Is there a function about this. I need to extract cap values form column A to column B. Cell contain only text values + space (no digits, no special characters)

Servlet
  • 1
  • 1
  • 1
  • Use regular expressions to perform the replacement: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops. All you need is to replace lowercase alphabets and spaces with nothing. – Terry Oct 21 '16 at 09:06
  • Thank you for replay. Is there a way with function, not with VBA? – Servlet Oct 21 '16 at 19:51
  • I am looking for something like this: http://www.mrexcel.com/forum/excel-questions/11707-returning-only-uppercase-letters-text-string.html#post54517 – Servlet Oct 21 '16 at 20:13
  • ... but described function doesnt work in Excel 2016 64 bit – Servlet Oct 21 '16 at 20:55

1 Answers1

0

I found solution but with VB. Insert code in normal module:

Function ExtractCap(Rng As Range)
Application.Volatile
ExtractCap = ""
For f = 1 To Len(Rng)
If Asc(Mid(Rng.Value, f, 1)) >= 65 And Asc(Mid(Rng.Value, f, 1)) <= 90 Then
ExtractCap = ExtractCap & Mid(Rng.Value, f, 1)
End If
Next f
End Function

And then use a formula such as this :

=ExtractCap(A1)

to show just the capital letters in A1.

Reference: http://www.mrexcel.com/forum/excel-questions/230646-extract-capital-letters-text-cell.html#post1127820

Servlet
  • 1
  • 1
  • 1