Need to Split Uppercase word in a sentance

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262
Hi Team,

I need one function where I can split word based on 'Uppercase'. Can anyone please help me with below requirement.

Please find the below reference data and also note that in this given data only we can find upper case word at the end of the sentence.

<table border="0" cellpadding="0" cellspacing="0" width="296"><colgroup><col style="mso-width-source:userset;mso-width-alt:10825;width:222pt" width="296"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:222pt" height="20" width="296">Bank of Ghana ACCRA </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Bank of Ghana AGONA SWEDRU </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Bank of Ghana TAKORADI </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Bank of Ghana SEFWI BOAKO </td> </tr> </tbody></table>

-- removed inline image ---


Thanks,
Harish Kumar
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mgm05267

Well-known Member
Joined
Nov 11, 2011
Messages
615
Hi Harish,

In your data, does the Bank of Ghana remains same..!!???

If so, you can use Text to columns option....
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
harinsh,


Sample raw data:


Excel Workbook
A
1Bank of Ghana ACCRA
2Bank of Ghana AGONA SWEDRU
3Bank of Ghana TAKORADI
4Bank of Ghana SEFWI BOAKO
5
Sheet1





After the macro:


Excel Workbook
A
1Bank of Ghana Accra
2Bank of Ghana Agona Swedru
3Bank of Ghana Takoradi
4Bank of Ghana Sefwi Boako
5
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub MakeProper()
' hiker95, 03/04/2012
' http://www.mrexcel.com/forum/showthread.php?t=618547
Dim c As Range, Sp, s As Long
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c = Trim(c)
  Sp = Split(c, " ")
  For s = LBound(Sp) To UBound(Sp)
    If Asc(Right(Sp(s), 1)) > 64 And Asc(Right(Sp(s), 1)) < 91 Then
      Sp(s) = Application.Proper(Sp(s))
    End If
  Next s
  c = Join(Sp, " ")
Next c
End Sub


Then run the MakeProper macro.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

I agree with mgm05267 that Text-to-Columns seems to be the easiest way to go.

If the strings start differently then perhaps try the following array formula (you need to press CTRL-SHIFT-ENTER not just ENTER or you will get a #N/A error):
Excel Workbook
AB
1Bank of Ghana ACCRAACCRA
2Bank of Ghana AGONA SWEDRUAGONA SWEDRU
3Bank of Ghana TAKORADITAKORADI
4Bank of Ghana SEFWI BOAKOSEFWI BOAKO
Sheet1
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262

ADVERTISEMENT

Hi Harish,

In your data, does the Bank of Ghana remains same..!!???

If so, you can use Text to columns option....


Hi mgm05267

Thanks for your reply.

No, it is not same and also length of sentence will not same please refer below further example.

<table border="0" cellpadding="0" cellspacing="0" height="156" width="396"><colgroup><col style="mso-width-source:userset;mso-width-alt:10825;width:222pt" width="296"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:222pt" height="20" width="296">Barclays Bank UNDP </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Barclays Bank TWIFO PRASO </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Barclays Bank WA </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Rural & Community Banks AFRAM RURAL BANK LTD-TEASE </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Ecobank ACCRA SHOPPING MALL </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Amalgamated Bank SPINTEX ROAD </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Fidelity Bank OKAISHIE
</td> </tr> </tbody></table>
Thanks
Harish Kumar
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262
Hi hiker95

Thanks for your reply.

Your macro is working fine but I want to split the capital letter word into different cell. In give macro I can change the word into small case.

Please help me in order to separate the capital word.

Thanks,
Harish Kumar
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262

ADVERTISEMENT

Hi circledchicken,

Thanks for your reply.

Text-to-Column not possible because length of text is different from one cell to another cell.

But I can able to get the output using your TRIM function. Some of cells the function did not return the results because of special character. That is fine this is more then enough in order to take the capital letter word.

Once again many thanks for quick reply.

Thanks,
Harish Kumar
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,998
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe this

Create a constant array ArrayLetters like

Formulas > Names Manager > New

Name: ArrayLetters
Refers to: ={"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

A B C
<TABLE style="WIDTH: 560pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=747><COLGROUP><COL style="WIDTH: 281pt; mso-width-source: userset; mso-width-alt: 13714" width=375><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 281pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=375>Barclays Bank UNDP</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 127pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=169>Barclays Bank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 152pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=203>UNDP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Barclays Bank TWIFO PRASO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Barclays Bank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TWIFO PRASO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Barclays Bank WA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Barclays Bank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>WA</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Rural & Community Banks AFRAM RURAL BANK LTD-LEASE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Rural & Community Banks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>AFRAM RURAL BANK LTD-LEASE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Ecobank ACCRA SHOPPING MALL</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ecobank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ACCRA SHOPPING MALL</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Amalgamated Bank SPINTEX ROAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Amalgamated Bank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>SPINTEX ROAD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Fidelity Bank OKAISHE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fidelity Bank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OKAISHE</TD></TR></TBODY></TABLE>


Array formula in B1
=LEFT(A1,MAX(IFERROR(FIND(ArrayLetters,A1),"")))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

Array formula in C1
=RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(ArrayLetters,A1),""))-1)


confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,998
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Complementing my previous post

The formulas work in Excel 2007 or higher

M.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
harinsh,


Sample raw data:


Excel Workbook
AB
1Bank of Ghana ACCRA
2Bank of Ghana AGONA SWEDRU
3Bank of Ghana TAKORADI
4Bank of Ghana SEFWI BOAKO
5Barclays Bank UNDP
6Barclays Bank TWIFO PRASO
7Barclays Bank WA
8Rural & Community Banks AFRAM RURAL BANK LTD-TEASE
9Ecobank ACCRA SHOPPING MALL
10Amalgamated Bank SPINTEX ROAD
11Fidelity Bank OKAISHIE
12
Sheet1





After the macro:


Excel Workbook
AB
1Bank of GhanaAccra
2Bank of GhanaAgona Swedru
3Bank of GhanaTakoradi
4Bank of GhanaSefwi Boako
5Barclays BankUndp
6Barclays BankTwifo Praso
7Barclays BankWa
8Rural & Community BanksAfram Rural Bank Ltd-Tease
9EcobankAccra Shopping Mall
10Amalgamated BankSpintex Road
11Fidelity BankOkaishie
12
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Sub MakeProperV2()
' hiker95, 03/05/2012
' http://www.mrexcel.com/forum/showthread.php?t=618547
Dim c As Range, Sp, s As Long, n As Long, t() As Variant
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c = Trim(c)
  Sp = Split(c, " ")
  n = 0
  For s = LBound(Sp) To UBound(Sp)
    If Asc(Right(Sp(s), 1)) > 64 And Asc(Right(Sp(s), 1)) < 91 Then
      Sp(s) = Application.Proper(Sp(s))
      n = n + 1
      ReDim Preserve t(1 To n)
      t(n) = Sp(s)
      Sp(s) = ""
    End If
  Next s
  c = Join(Sp, " ")
  c = Trim(c)
  If n = 1 Then
    c.Offset(, 1).Value = t
  Else
    c.Offset(, 1).Value = Join(t, " ")
  End If
  Erase t
Next c
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the MakeProperV2 macro.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,110
Messages
5,768,150
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top