Need to Split Uppercase word in a sentance

[harinsh]

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

 

[hiker95]

harinsh,

Some of cells the function did not return the results because of special character.

If my latest macro does not work correctly, then we will need accurate screenshot of your raw data.

And, we will need a screenshot of the results, manually formatted by you.


To attach screenshots, see below in my Signature block:
Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block:
You can upload your workbook to Box Net
mark the workbook for sharing
and provide us with a link to your workbook.

 

[harinsh]

Hi hiker95,

You are macro absolutely working fine. Many thanks for your help. For me It would take more time to understand your code.

You people are really genius

Thanks.
Harish Kumar

 

[hiker95]

harinsh,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

 

[Marin30]

Hello,

I can not split like this :
Could you please hellp me
[TABLE="width: 747"]
<tbody>[TR]
[TD="class: xl65, width: 375, bgcolor: transparent"]Barclays Bank UNDP
[/TD]
[TD="class: xl65, width: 169, bgcolor: transparent"]Barclays Bank
[/TD]
[TD="class: xl65, width: 203, bgcolor: transparent"]UNDP
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Barclays Bank TWIFO PRASO
[/TD]
[TD="class: xl65, bgcolor: transparent"]Barclays Bank
[/TD]
[TD="class: xl65, bgcolor: transparent"]TWIFO PRASO
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Barclays Bank WA
[/TD]
[TD="class: xl65, bgcolor: transparent"]Barclays Bank
[/TD]
[TD="class: xl65, bgcolor: transparent"]WA
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Rural & Community Banks AFRAM RURAL BANK LTD-LEASE
[/TD]
[TD="class: xl65, bgcolor: transparent"]Rural & Community Banks
[/TD]
[TD="class: xl65, bgcolor: transparent"]AFRAM RURAL BANK LTD-LEASE
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Ecobank ACCRA SHOPPING MALL
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ecobank
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACCRA SHOPPING MALL
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Amalgamated Bank SPINTEX ROAD
[/TD]
[TD="class: xl65, bgcolor: transparent"]Amalgamated Bank
[/TD]
[TD="class: xl65, bgcolor: transparent"]SPINTEX ROAD



[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Fidelity Bank OKAISHE
[/TD]
[TD="class: xl65, bgcolor: transparent"]Fidelity Bank
[/TD]
[TD="class: xl65, bgcolor: transparent"]OKAISHE
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Marin30,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider based on your flat text display.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C
1	Barclays Bank UNDP
2	Barclays Bank TWIFO PRASO
3	Barclays Bank WA
4	Rural & Community Banks AFRAM RURAL BANK LTD-LEASE
5	Ecobank ACCRA SHOPPING MALL
6	Amalgamated Bank SPINTEX ROAD
7	Fidelity Bank OKAISHE
8
Sheet1

And, after the macro:

Excel 2007
	A	B	C
1	Barclays Bank UNDP	Barclays Bank	UNDP
2	Barclays Bank TWIFO PRASO	Barclays Bank	TWIFO PRASO
3	Barclays Bank WA	Barclays Bank	WA
4	Rural & Community Banks AFRAM RURAL BANK LTD-LEASE	Rural Community Banks	AFRAM RURAL BANK LTD-LEASE
5	Ecobank ACCRA SHOPPING MALL	Ecobank	ACCRA SHOPPING MALL
6	Amalgamated Bank SPINTEX ROAD	Amalgamated Bank	SPINTEX ROAD
7	Fidelity Bank OKAISHE	Fidelity Bank	OKAISHE
8
Sheet1

I see that I missed the & character.

Be back in a little while.

 

[hiker95]

Marin30,

Here is a macro solution for you to consider based on your flat text display.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C
1	Barclays Bank UNDP
2	Barclays Bank TWIFO PRASO
3	Barclays Bank WA
4	Rural & Community Banks AFRAM RURAL BANK LTD-LEASE
5	Ecobank ACCRA SHOPPING MALL
6	Amalgamated Bank SPINTEX ROAD
7	Fidelity Bank OKAISHE
8
Sheet1

And, after the macro:

Excel 2007
	A	B	C
1	Barclays Bank UNDP	Barclays Bank	UNDP
2	Barclays Bank TWIFO PRASO	Barclays Bank	TWIFO PRASO
3	Barclays Bank WA	Barclays Bank	WA
4	Rural & Community Banks AFRAM RURAL BANK LTD-LEASE	Rural & Community Banks	AFRAM RURAL BANK LTD-LEASE
5	Ecobank ACCRA SHOPPING MALL	Ecobank	ACCRA SHOPPING MALL
6	Amalgamated Bank SPINTEX ROAD	Amalgamated Bank	SPINTEX ROAD
7	Fidelity Bank OKAISHE	Fidelity Bank	OKAISHE
8
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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub SplitProperCaseUpperCasePlus()
' hiker95, 10/20/2016, ME618547
Dim c As Range, SP, SU, s As Long, n As Long, tu() As Variant, tp() As Variant
Application.ScreenUpdating = False
Columns("B:C").ClearContents
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c = Trim(c)
  SU = Split(c, " ")
  n = 0
  For s = LBound(SU) To UBound(SU)
    If Asc(Right(SU(s), 1)) > 64 And Asc(Right(SU(s), 1)) < 91 Then
      n = n + 1
      ReDim Preserve tu(1 To n)
      tu(n) = SU(s)
      SU(s) = ""
    End If
  Next s
  If n = 1 Then
    c.Offset(, 2).Value = tu
  Else
    c.Offset(, 2).Value = Join(tu, " ")
  End If
  Erase tu
  SP = Split(c, " ")
  n = 0
  For s = LBound(SP) To UBound(SP)
    If Asc(Right(SP(s), 1)) = 38 Or Asc(Right(SP(s), 1)) > 96 And Asc(Right(SP(s), 1)) < 123 Then
      SP(s) = Application.Proper(SP(s))
      n = n + 1
      ReDim Preserve tp(1 To n)
      tp(n) = SP(s)
      SP(s) = ""
    End If
  Next s
  If n = 1 Then
    c.Offset(, 1).Value = tp
  Else
    c.Offset(, 1).Value = Join(tp, " ")
  End If
  Erase tp
Next c
Columns("B:C").AutoFit
Application.ScreenUpdating = True
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the SplitProperCaseUpperCasePlus macro.

 

[Marin30]

I am sorry but I do not understand your post. Do we have some funkcion for this problem?

 

[hiker95]

I am sorry but I do not understand your post. Do we have some funkcion for this problem?

Marin30,

You did not ask for a Function to solve your request.

The theme of this entire thread has been for a macro solution.


See reply #8 for two array formulas to solve your request.


If you are looking for a Function to solver your request, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Marin30]

Hi Hiker95,

I managed to solve this , thank you very mouch.
but also I would like to know do we have the funkcion for this.
thnx
have a nice day too

 

[hiker95]

Hi Hiker95,

I managed to solve this , thank you very mouch.

You are welcome. Glad that you were able to solve your request.

but also I would like to know do we have the funkcion for this.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.