Excel - Find Capital Letter

Asked By Boogi on 22-Jan-09 10:23 AM
Example: In A1 BoogieMByron. How can make it as in A2 Boogie M Byron. There's
hundreds of names whereby Firstname,MI,LastName are all in one word. I want
to cleate a space before and after Middle initial. Thanks for your help.




Bernie Deitrick replied on 22-Jan-09 10:48 AM
Boogie,

You need a few formulas to do that:

In A2, enter using Ctrl-Shift-Enter:
=FIND(CHAR(MIN(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1)))),A1,2)

In A3, enter
=LEFT(A1,A2-1)&" " &MID(A1,A2,LEN(A1))

In A4, enter using Ctrl-Shift-Enter
=FIND(CHAR(MIN(CODE(MID(A3,ROW(INDIRECT(A2+1& ":"&LEN(A3))),1)))),A3,2)+1

In A5, enter
=LEFT(A3,A4-1)&" " &MID(A3,A4,LEN(A3))

HTH,
Bernie
MS Excel MVP
GarysStuden replied on 22-Jan-09 10:56 AM
Try the following user defined function:

Function reformit(r As Range) As String
Dim v As String, done As Boolean
Dim chrct As String
v = r.Value
reformit = Left(v, 1)
done = False
For i = 2 To Len(v)
chrct = Mid(v, i, 1)
If chrct = UCase(chrct) And Not done Then
reformit = reformit & " " & chrct & " "
done = True
Else
reformit = reformit & chrct
End If
Next
End Function

so if A1 contains:
AfjakfhBNjfdlskf

the formula:
=reformit(A1) will display Afjakfh B Njfdlskf


--
Gary''s Student - gsnu200828
Glenn replied on 22-Jan-09 11:19 AM
In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"

***This is an array formula.  Commit with CTRL+SHIFT+ENTER  ***
Glenn replied on 22-Jan-09 11:19 AM
In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"

***  This is an array formula.  Commit with CTRL+SHIFT+ENTER  ***
Glenn replied on 22-Jan-09 11:24 AM
Maybe better with forced carriage returns so you don't lose the spaces...

=LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&" "&
MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1)&" "&
MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LEN(A1))
Glenn replied on 22-Jan-09 11:25 AM
Sorry for the duplicate post...
Roger Govier replied on 22-Jan-09 11:46 AM
Hi

On the assumption that there may be cases with no Middle Initial, several
middle initials and spaces already within the name, then the following code
should work for you.
I would work on a COPY of your data, in case it doesn't do what you want.


I used column K for my data.
Change the column letter to suit your use.

Sub SplitName()
Dim i As Long, j As Long, lr As Long
Dim oldname As String, newname As String
Dim nchar1 As String, nchar2 As String
lr = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To lr

oldname = Cells(i, "K").Value
If oldname <> "" Then
newname = Left(oldname, 1)
For j = 2 To Len(oldname)
nchar1 = Mid(oldname, j - 1, 1)
nchar2 = Mid(oldname, j, 1)
If nchar1 = " " Then
newname = newname + nchar2
ElseIf nchar2 = " " Then
newname = newname & nchar2
ElseIf UCase(nchar2) = nchar2 And _
UCase(nchar1) = nchar1 Then
newname = newname & " " & nchar2
ElseIf UCase(nchar2) = nchar2 And _
UCase(nchar1) <> nchar1 Then
newname = newname & " " & nchar2
Else
newname = newname & nchar2
End If
Next j

Cells(i, "K") = newname
End If
Next i

End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

--
Regards
Roger Govier
Harlan Grove replied on 26-Jan-09 07:20 AM
Boogie <Boo...@> wrote...

Not possible to handle general cases (number of middle names ranging
from 0 to 4) easily using formulas. Also begs the question whether you
have any Scottish or Irish names, e.g., MacNabb, McNeely, O'Brien,
since I doubt you'd want the following transformations.

JohnEMacNab   ->  John E Mac Nabb
SusanMcNeely  ->  Susan Mc Neely
FXO'Brien     ->  F X O' Brien

If you only need to perform this task infrequently, best to use
regular expressions in macros. There are many regular expression
search & replace macros already in the newsgroup archives.
Glenn replied on 22-Jan-09 01:12 PM
And shortened to this with the removal of unnecessary TRANSPOSE and variable
lengths:

=LEFT(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0))&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+1,1)&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+2,LEN(A1))

Still an array formula.  And acknowledging Harlan's warnings of strange results
with non-standard names.
Harlan Grove replied on 26-Jan-09 07:20 AM
Glenn <addr...@not.valid> wrote...
...
...

If many such formulas are needed, maybe simpler to defined the
following names.

UCS
="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

UCA
={"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";

Then use array formulas like

=REPLACE(A1,MIN(FIND(UCA,A1&UCS,2)),1," "&MID(A1,MIN(FIND(UCA,A1&UCS,
2)),1)&" ")
Boogi replied on 23-Jan-09 12:06 PM
For all of you who contributed to my posting, you all Rock. Thank you!. I
just want let Glenn know that I used his formula since it's a little easier
for me (a newbie) to figure out what he suggested. All the rest, you gave me
something that I have to figure out for a couple of days. :-). Anyways, lots
of thanks.
Glenn replied on 23-Jan-09 12:25 PM
Glad I could help.  Hopefully, you are using the shortest of my suggestions:

=LEFT(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0))&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+1,1)&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+2,LEN(A1))