How to format differently 2 parts of same string?

[ValerieT]

Hello

I've got employee names having this format:

IPU - XXXXX - Lastname, Firstname (in one cell)

How can I process 'IPU - XXXXX - " in upper case and the rest in Proper case? knowing that XXXX doesn't have a fix number of digits?

My guess is somehow counting from end to "-" = part to be Proper
then
Total lengh - previous result = part to be Upper?

Is there something simpler?

[JKwan]

do you just want to isolate "IPU - XXXXX"? If so, try this:


Sub test()
For i = 1 To 22
temp = Split(Cells(i, "A"), " - ")
Cells(i, "B") = temp(0) & " - " & temp(1)
Cells(i, "C") = Right(Cells(i, "A"), Len(Cells(i, "A")) - Len(Cells(i, "B")) - 3)
Next i
End Sub


Updated with isolating names

[skulakowski]

Your approach would be my approach.


Dim InputString As String
Dim IPUCount As Integer
Dim IPUString As String
Dim NameString As String
Dim OutputString As String
InputString = ActiveCell.Value
IPUCount = WorksheetFunction.Find(IPUString, "-", 6)
'the first "-" falls at position 5, so second "-" must be position 6 or more
IPUString = Left$(InputString, IPUCount)
NameString = Mid$(InputString, IPUCount + 1, 100)
IPUString = vbUpperCase(IPUString)
NameString = Proper(NameString)
OutputString = IPUString & NameString

[SamT]

Sub SamT()
Dim Cel As Range
Dim CelString As String
Dim MidNumbers As String 'if "XXXX" = numbers
Dim IPUString As String 'If "XXXX" = Characters
Dim EmpName As String
Dim Dash1
Dim Dash2
'Loop thru Cells here
'Next 2 Lines are for code development testing
Dim Result As String
CelString = "IPU - one2345 - lastname, firstname"
'Uncomment next line for use in your macro
'CelString = Cel.Value
Const XXXXIsNumbers As Boolean = False 'Change to suit
Dash1 = InStr(1, CelString, "-")
Dash2 = InStr(Dash1 + 1, CelString, "-")
If XXXXIsNumbers Then
IPUString = UCase(Left(CelString, 3))
MidNumbers = Mid(CelString, Dash1 - 1, (Dash2 - Dash1 + 3))
EmpName = Right(CelString, Len(CelString) - (Dash2 + 1))
EmpName = StrConv(EmpName, vbProperCase)
Result = IPUString & MidNumbers & EmpName
End If
If Not XXXXIsNumbers Then
IPUString = UCase(Left(CelString, Dash2 + 1))
EmpName = Right(CelString, Len(CelString) - (Dash2 + 1))
EmpName = StrConv(EmpName, vbProperCase)
Result = IPUString & EmpName
End If
End Sub

[ValerieT]

Hello all

I went with Skula solution thinking it was easier.. (I am a beginning beginner ;-) I had to fight against some error messages and re-write some lines but it works well. (Search vsf Find and vbUpperCase format...)

I also like SAmT solution, I'll do some exercice with it..

THANKS YOU so much everybody!

[snb]

If these data are in A1:A40 e.g.
you can use this oneliner:


Sub M_snb()
[A1:A40] = [if(A1:A40="","",upper(left(A1:A40,find("-",A!:A40,6))) & proper(Mid(A1:A40,find("-",A1:A40,6)+1,100)))]
End Sub