Find case sensitive difference between 2 cells

[ESACAWIP]

Hi,

Hope you can help, having a lot of trouble finding a solution. I have a large list of facilities names with incorrect formatting

BHCW boris health of wisconsin (Original)
Bhcw Boris Health of Wisconsin (After using =Proper formula to format the text)

Everything looks fine expect for the acronym at the front which should read "BHCW" instead. I was thinking is it possible to have a vba formula to highlight the case sensitive differences in each letter? That way I find more easily find issues and edit manually.

Bhcw Boris Health of Wisconsin


Thank you!

 

[maabadi]

What about this formula if your Data is in A2:

=UPPER(LEFT(A2,FIND(" ",A2,1)))&PROPER(RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))

Book17.xlsm
	A	B	C
1
2	Bhcw Boris Health of Wisconsin		BHCW Boris Health Of Wisconsin
3
Temp

Cell Formulas
Range	Formula
C2	C2	=UPPER(LEFT(A2,FIND(" ",A2,1)))&PROPER(RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))

 

[ESACAWIP]

Hi maabadi,
Thanks for the response

Sorry I forgot to mention my list contains many inconsistencies. I believe no formula can resolve this so my goal is to use the "=proper" formula and then have another VBA to highlight the differences so I can manually edit.

ie:

Bank of the West (CMPT Branch)
CDE Hospital Center
Johnson (SGD) Memorial

 

[maabadi]

Please upload your Example file with XL2BB ADDIN (preferable) or upload at free hosting site e.g. www.dropbox.com , GoogleDrive or OneDrive and Insert Link here.

 

[maabadi]

This code only change Lower case words to Proper case and don't touch Uppercase for all Data at column A. Check it:

Sub MergeSheets2()
Dim xArr As Variant, xI As Integer, PCase As String
Dim i As Long, Lr As Long, j As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To Lr
If Range("A" & i).Value <> "" Then
xArr = Split(Range("A" & i).Value, " ")
For xI = 0 To UBound(xArr)
If UCase(xArr(xI)) = xArr(xI) Then
Else
PCase = StrConv(xArr(xI), vbProperCase)
xArr(xI) = PCase
End If
Next xI
Range("E" & i).Value = Join(xArr, " ")
End If
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

 

[ESACAWIP]

Thank you maabadi!


Do you know if something like this is possible for column C

Original	Edited	Difference
700 West Fabyan Parkway	700 West Fabyan Pkwy	Pkwy
700 West Fabyan Parkway Nw	700 West Fabyan Parkway NW	NW
700 West Fabyan Parkway Nw	700 West Fabyan Pkwy NW	Pkwy, NW
700 West Fabyan Parkway Nw	700 W Fabyan Pkwy NW	W, Pkwy, NW

 

[maabadi]

if you have index for replacing specific word is separate cell, it con done. but without index , I cannot , maybe others have option for you.
And please Upload file with example and all desired results, if you prepare index words and replacing phrases.