split and join data case sensitive

already

Board Regular
Joined
Nov 11, 2008
Messages
179
Hi

I have a large data base with 8 columns (imported from the web)
The data consists of names in uppercase and titles in propercase.
The name could be in 1,2 or 3 columns and also the titels coud be spread in more columns.

The goal is to join the uppercase words in one cell and the propercase words in another.

ex 4 columns

1/ LENNY |KRAVITZ |Black And White |America

ex 3 columns
2/ THE KOOKS | Junk Of The | Heart

should be converted in

1/ LENNY KRAVITZ | Black And White America
2/ THE KOOKS | Junk Of The Heart

etc

Thanks in advance for your help

Kind regards

Al
 
Last edited:

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
This macro will create your items in columns J:K

Code:
Option Explicit
Option Compare Binary   'case sensitive

Sub Reformat()
Dim LR As Long, rw As Long, BUF As String, buf2 As String
Dim RNG As Range, NmStr As Range

LR = Range("A" & Rows.Count).End(xlUp).Row
Range("J:K").ClearContents

For rw = 2 To LR
    Set RNG = Rows(rw).SpecialCells(xlConstants)
    For Each NmStr In RNG
        If NmStr = UCase(NmStr) Then
            BUF = BUF & " " & NmStr
        Else
            buf2 = buf2 & " " & NmStr
        End If
    Next NmStr
    Range("J" & rw) = Trim(BUF)
    Range("K" & rw) = Trim(buf2)
    BUF = ""
    buf2 = ""
Next rw
            
Range("J:K").Columns.AutoFit
End Sub
Excel Workbook
ABCDEFGHIJK
1
2LENNYKRAVITZBlack And WhiteAmericaLENNY KRAVITZBlack And White America
3THE KOOKSJunk Of TheHeartTHE KOOKSJunk Of The Heart
4BOBJOHNDILLONOnly InAmericaBOB JOHN DILLONOnly In America
5
6
Blad2
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
Here is another macro for you to consider...

Code:
Sub CombineAllUpperCaseAndAllProperCase()
  Dim X As Long, Z As Long, LastCol As Long, Cell As Range, Upper As String, Proper As String
  Const StartRow As Long = 2
  Const FirstOuputColumn As String = "G"
  For X = StartRow To Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    Upper = ""
    Proper = ""
    For Z = 1 To LastCol
      If Not Cells(X, Z).Value Like "*[!A-Z ]*" Then
        Upper = Upper & Cells(X, Z) & " "
      Else
        Proper = Proper & Cells(X, Z) & " "
      End If
    Next
    Cells(X, FirstOuputColumn).Value = Trim(Upper)
    Cells(X, FirstOuputColumn).Offset(, 1).Value = Trim(Proper)
  Next
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,793
=trim(rept(exact(proper(a1),a1),a1)&" "&rept(exact(proper(b1),b1),b1)&" "&rept(exact(proper(C1),C1),C1)&" "&rept(exact(proper(d1),d1),d1))
 

Forum statistics

Threads
1,082,128
Messages
5,363,325
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top