Excel macro question w/ strings 2

[lareya]

Hi all,
I have a staff file name which has the lastname,firstname in one column. I want to take all the names and regardless how long the names are in character, I want to have the lastname be four charachter, and the firstname in four charachters.

thus:
pierce,marta will be added to the H column like this: pier,mart

the data is set up like this:
A B D F G H
Staff ID Staff Name ID Staff Type Service
2471 GABRIEL,CASSIE 94364 Anesthesia Resident GENERAL
1389 KROLL,PETER B. 93215 Anesthesia Resident GENERAL
1440 QUAST,MICHAEL L. 93243 Anesthesia Resident GENERAL
2200 RAMSEY,CARL 94126 Anesthesia Resident GENERAL
1220 FARROKHI,FARZAD 92808 Anesthesia Resident PAIN MANAGEMENT
480 HAWORTH,JOHN 92059 Anesthesia Resident PLASTIC/RECONSTRUCT*

Can s/o help me do this? I am hoping that a macro will do this. I will have several staff files that will need this same thing done.

Thank you,
Lareya
MS Excel 2000

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Crystal XI; SQL database; MS Windows 2K; ORSOS/One Call Hospital Scheduling System v10.1.2; Huge Newbie to Crystal! Operating Room RN Analyst

 

[KenPuls]

Hello,

Assuming that your header row is in row 1, there is nothing after the last row of data in column B, and that every name contains a comma, you could use the following:

Sub TruncNames()
Dim cl As Range
Dim ary() As String

Application.ScreenUpdating = False

With ActiveSheet
    For Each cl In .Range(.Cells(2, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 2))
        ary() = Split(cl.Value, ",")
        cl.Value = Left(ary(0), 4) & "," & Left(ary(1), 4)
    Next cl
End With

Application.ScreenUpdating = True
End Sub

It does use a loop, so will get slow if you have thousands of names to do.

HTH,

Ken Puls, CMA

http://www.excelguru.ca

 

[Fenrirshowl]

If you want to stay away from macros you can do it with the inbuilt functions. Put the following into the free columns (assumed to start at column H)

Col H: Find stringh length
=LEN($B3)

Col I: Find the comma
=FIND(",",$B3)

Col J: Find first 4 (or less) letters of the surname
=LEFT($B3,MIN(4,I3-1))

Col K: Find first 4 (or less) letters of the first name
=LEFT(RIGHT($B3,H3-I3),MIN(4,H3-I3))

Col L: Combine them with a comma
=J3&","&K3

You can run this for all lines and then copy and pastespecial to enter it as text if you want and then delete the preparation columns.

Fen

 

[lareya]

Thank you!
This was exactly what I needed. I even can use it for my other staff files! I understand the inbuilt functions much better than the macro. I will try to figure out the macro.

Thank you!

Lareya

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Crystal XI; SQL database; MS Windows 2K; ORSOS/One Call Hospital Scheduling System v10.1.2; Huge Newbie to Crystal! Operating Room RN Analyst