Find Capital Letter

  • Thread starter Thread starter Boogie
  • Start date Start date
B

Boogie

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.
 
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
 
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
 
Boogie said:
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.
Click to expand...

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))&"
"&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))

***This is an array formula. Commit with CTRL+SHIFT+ENTER ***
 
Boogie said:
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.
Click to expand...

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))&"
"&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))

*** This is an array formula. Commit with CTRL+SHIFT+ENTER ***
 
Glenn said:
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))&"
"&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))


***This is an array formula. Commit with CTRL+SHIFT+ENTER ***
Click to expand...


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))
 
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
 
Boogie said:
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.
Click to expand...

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 said:
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))
Click to expand...


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.
 
Glenn said:
=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))
Click to expand...
....

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";
"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"}

Then use array formulas like

=REPLACE(A1,MIN(FIND(UCA,A1&UCS,2)),1," "&MID(A1,MIN(FIND(UCA,A1&UCS,
2)),1)&" ")
 
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.
 
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))
 
Back
Top