How do we separate text by upper / lower case

[mohammad_t]

Hello everyone,

I have a long list of company names. Each company's name has got combined like "AstroPhysics" instead of "Astro Physics".

Can anyone help me how to separate combined text by reference to where a letter appears in upper / lower case?

Thanks & Regards,
Talha

 

[mikerickson]

This CSE will return the last bit of text that is begun with a capital

=MID(A1,MATCH(TRUE,EXACT(LOWER(MID(A1,COLUMN(A:IU),255)), MID(A1,COLUMN(A:IU),255)),0)-1,255-1)

Enter this with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Rick Rothstein]

Hello everyone,

I have a long list of company names. Each company's name has got combined like "AstroPhysics" instead of "Astro Physics".

Can anyone help me how to separate combined text by reference to where a letter appears in upper / lower case?

Assuming you want to physically change the original data within their cells, give the following macro a try. Note... you did not tell us the column your company names were in, so I assumed Column A... if that guess was not correct, then change the "A" in the highlighted code line to the actual letter designation for the column containing your company names.

Sub AddSeparatingSpacesToNames()
  Dim R As Long, C As Long, Arr As Variant
  Const NameCol As String = "A"
  Arr = Range(Cells(1, NameCol), Cells(Rows.Count, NameCol).End(xlUp))
  For R = 1 To UBound(Arr)
    For C = Len(Arr(R, 1)) To 2 Step -1
      If Mid(Arr(R, 1), C, 2) Like "[a-z][A-Z]" Then
        Arr(R, 1) = Application.Replace(Arr(R, 1), C + 1, 0, " ")
      End If
    Next
  Next
  Cells(1, NameCol).Resize(UBound(Arr)) = Arr
End Sub

 

[Armando Montes]

Try:
http://www.mrexcel.com/forum/<wbr>showthread.php?p=2095356#<wbr>post2095356
pgc01
Sub CamelCase()
Dim rC As Range

With CreateObject("VBScript.RegExp"<wbr>)
.Pattern = "([a-z])([A-Z])"
.Global = True
For Each rC In Selection
rC.Parse "[" & .Replace(rC, "$1][$2") & "]", rC.Offset(, 2)
Next rC
End With
End Sub

 

[mohammad_t]

Thanks mikerickson, Rick and Armando. I guess I'm too new to Excel to apply your suggestions .

 

[Rick Rothstein]

Thanks mikerickson, Rick and Armando. I guess I'm too new to Excel to apply your suggestions .

For the code I posted in Message #3....

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (AddSeparatingSpacesToNames) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm).

NOTE: Make sure to read the part where I describe what to do with the red highlighted text in Message #3.

 

[mikerickson]

My formula was an Excel CSE formula
If your string is in A1, use the formula as written, if not change the A1's in the formula to match your situation but leave the A:UI reference alone.

put the formula in a cell, but press Ctrl-Shift-Enter simultaneously to enter the formula, rather then only Enter.

 

[nguyenhoa]

This CSE will return the last bit of text that is begun with a capital

=MID(A1,MATCH(TRUE,EXACT(LOWER(MID(A1,COLUMN(A:IU),255)), MID(A1,COLUMN(A:IU),255)),0)-1,255-1)

Enter this with Ctrl-Shift-Enter (Cmd+Return for Mac)

If it is a text with capital letters, how: "mao GT100 anh" separates GT100

 

[Georgiboy]

This thread is nearly 10 years old - you will get better help if you start a new thread.