Need to Split Uppercase word in a sentance

H

harinsh

Active Member
Joined
Feb 7, 2012
Messages
273
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi Harish,

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

If so, you can use Text to columns option....
 
Upvote 0
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.
 
Upvote 0
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 {}.
 
Upvote 0
mgm05267 said:
Hi Harish,

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

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


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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Complementing my previous post

The formulas work in Excel 2007 or higher

M.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
Splitting lower case from uppercase
2 3
Replies
21
Views
1K
JLGWhiz
JLGWhiz
zookeepertx
  • Question
Need to validate that all documents are appearing in a file
Replies
4
Views
787
zookeepertx
zookeepertx
lrobbo314
  • Question
Arrays and Table Filtering
Replies
3
Views
792
MickG
M
P
  • Question
Split a column
Replies
2
Views
614
Panoos64
P
P
  • Question
Clear specific word VBA
2
Replies
11
Views
1K
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,214,951
Messages
6,122,442
Members
449,083
Latest member
Ava19

Share this page

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
Back
Top