Spliting 2 Words in Excel before the last capital Letter?

Question

I am trying to figure out how to split a single word into 2 separate words in excel.

e.g. input word: BTCBitcoin

  Desired output:  BTC Bitcoin

any suggestions?

Thanks.

Answer 1

To put a space before the last uppercase letter in a string, use this:

=REPLACE(A1,AGGREGATE(14,6,ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)))/(CODE(UPPER(MID(A1,ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1))),1)))=CODE(MID(A1,ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1))),1))),1),0," ")

It will iterate through the string and find the last capital letter, then return its relative position to the REPLACE function which, since the third criterion in that function is 0, will place a space in front of that position.

Answer 2

With data in A1, in B1 enter the array formula:

=LEFT(A1,MAX(SIGN(1-(INT(CODE(MID(A1,ROW(A$1:INDIRECT("A"&LEN(A1))),1))/31)-2)^2)*ROW(A$1:INDIRECT("A"&LEN(A1))))-1)

and in C1 enter the array formula:

=MID(A1,MAX(SIGN(1-(INT(CODE(MID(A1,ROW(A$1:INDIRECT("A"&LEN(A1))),1))/31)-2)^2)*ROW(A$1:INDIRECT("A"&LEN(A1)))),9999)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Answer 3

Or you can use UDF - save below code to separate module and use!

Function LAST_CAPITAL(ByVal asw As String) As String
Dim i As Integer, cntr As Integer
cntr = -1
For i = 1 To Len(asw)
  If Mid(asw, i, 1) = UCase(Mid(asw, i, 1)) And Mid(asw, i, 1) <> " " Then cntr = cntr + 1
Next i
If cntr > 0 Then
  LAST_CAPITAL = Left(asw, cntr) & " " & Right(asw, Len(asw) - cntr)
Else
  LAST_CAPITAL = asw
End If
End Function

Answer 4

say the text 'BTCBitcoin' is in cell F16

When you use =LEFT(F16,3) at cell G16, you get 'BTC' for cell G16 When you use =RIGHT(F16,7) at cell G17, you get 'Bitcoin' for cell G17 When you use =CONCAT(G16, " ", G17) at cell G18, you get 'BTC Bitcoin' for cell G18.

You can just do all that in one fell swoop at cell G16 by =CONCAT(LEFT(F16,3)," ",RIGHT(F16,7))