Adding a space before the second capital letter in a cell

Question

I have an Excel sheet with a column of names eachnmae begins with firstname capitalize and lastname capitalized. like so JohnSmith

I like to know if there's a way in excel to take JohnSmith and make it John Smith . Please help

Thank you

I'd suggest looking [here](http://www.excelbanter.com/showthread.php?t=219564) as a starting point. There are a few approaches that people have used to accomplish something like what you are looking for. — Derrik, Aug 01 '14 at 20:58

score 1 · Answer 1 · answered Jul 22 '17 at 08:05

The best answer would be to use the macros in the excel.

Function AddSpaces(pValue As String) As String

Dim xOut As String
Dim iCounter As Integer
Dim xAsc As Variant

xOut = VBA.Left(pValue, 1)
For iCounter = 2 To VBA.Len(pValue)
   xAsc = VBA.Asc(VBA.Mid(pValue, iCounter, 1))
   If xAsc >= 65 And xAsc <= 90 Then

        If VBA.Mid(pValue, iCounter - 1, 2) = "ID" Then
        'StrComp(str1, str2, vbTextCompare)
            xOut = xOut & VBA.Mid(pValue, iCounter, 1)
        Else
            xOut = xOut & " " & VBA.Mid(pValue, iCounter, 1)
        End If
   Else
      xOut = xOut & VBA.Mid(pValue, iCounter, 1)
   End If
Next
AddSpaces = xOut
End Function

Not my code originally, copied from elsewhere, changed few variables to support explicit declaration of variables.

From where did you copy it? You need to mention sources if the code is not your own. — JJJ, Jul 22 '17 at 20:56
JJJ - Here is is the source https://stackoverflow.com/questions/43237341/vba-put-a-space-after-or-before-a-upppercase-letter-in-a-string — Anand, Jul 25 '17 at 09:51

score 0 · Accepted Answer · answered Aug 05 '14 at 11:52

0

As already suggested you may use multiple formulate listed in the thread linked to by @Derrik, but I think they all look really long and cumbersome. My personal favourite is:

=trim(REGEX.SUBSTITUTE(A1,"([A-Z])"," [1]"))

This requires Morefunc Addon (see below, it has a host of other useful functions too)

MOREFUNC ADDON

here is the last working download link I found
here is a good installation walk-through video

answered Aug 05 '14 at 11:52

user3616725

3,485
1
18
27

as far as I know, yes, provided you use 32 bit, not 64 bit version. But unfortunately cannot test for sure. If the install does not seem to work follow instructions under point 4 on this page: http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/ – user3616725 Aug 06 '14 at 14:04

score 0 · Answer 3 · answered Aug 05 '14 at 19:34

From Derik's link, you could use this array formula (means you have to confirm it with ctrl+shift+enter instead of just enter):

=MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65:90"))),C2,2)),"",FIND(CHAR(ROW(INDIRECT("65:90"))),C2,2)))

Though I modified it to start at the second character. This will give you the position of the first capital, ignoring the first character. So if C2 is "JohnSmith" this will give you 5, because the S is in the 5th position, in cell D2.

Now that you know where the last name starts, you can piece the full name with space together using in E2:

=LEFT(C2,D2-1)&" "&RIGHT(C2,LEN(C2)-D2+1)

This takes the left 5-1=4 characters John, concatenates it with a space, and the last name which is the right 9-5+1=5, since 9 is the length of the whole name, which is Smith, so you end up with "John Smith".

This method will only add a single space to where ever it finds the first capital letter and not to all capital letters. — Dan, Aug 05 '14 at 19:35

Adding a space before the second capital letter in a cell

3 Answers3

Linked