Auto correct Name Format Entered (xls 97)

Topic

New Reply

I feel like I am abusing this board but is it possible if I have a cell where a name should be entered, i.e Johnatan Smith. If the user enters John Smith, J Smith, or Jo. Smith can I auto correct in excel to transform it to J. Smith. I want all of those cells to look the same. They should be First Initial,Period, Space, Last. (J. Smith). Thanks a million guys I owe you all. Once i learn something i will share also.

Ed

Reply | Quote

Replies

It will be easier to manage if you have First, Middle and Last in separate cells and then clean them up via ether formulas or VBA , and then finally concatenate the results into a fourth cell. You’ll also need to consider rules to handle, “Jr., Sr.” and “II”, “III”, etc.

Reply | Quote

You could use the Worksheet_Change event to alter the cell entry to what is suitable. Something like the following might work :-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("A:A")) _
            Is Nothing Then
        Dim strCell As String
        Dim strRet As String
        Dim intMid As Integer
        strCell = Target.Value
        If Not IsNumeric(strCell) And strCell  "" Then
            Application.EnableEvents = False
            intMid = InStr(1, strCell, " ", 1)
            strRet = Left(strCell, 1) & ". " & _
                Right(strCell, Len(strCell) - intMid)
            Target.Value = strRet
            Application.EnableEvents = True
        End If
    End If
End Sub

Right click on the sheet tab of the workseet you want to work with, select View Code and place the above code in the main window. It is setup to monitor column A , but you can change that by altering Range(“A:A”) to whatever columns you want. Ideally I expect it should trap more erroneous entries, but try it and see if it works in your situation.

Andrew C

Reply | Quote

Hi Andrew the code works fine once i fixed it for the range i needed. Your directions were great. If a person had a middle name could i edit this could to display it as J. D. Smith. So its First Initial., Middle Initial., Last. Sorry for the bother, but its something I just realized migth be a problem. You have no idea how much youve helped, thanks.

Reply | Quote

Never mind the middle initial stuff, not using that anymore.

Reply | Quote

Thats ok with me.

Cheers

Reply | Quote

For some reason i am getting a run time error 13 problem when i use the code. This is what i am doing. A name gets selected from a drop down list on sheet 2 in the format ( First, Last). i then need to have this appear as (F. Last) on sheet 1. I figured i could copy the info from the list in sheet2 into sheet 3 and run the code from sheet 3.i could then copy sheet 3 into sheet 1. nobody sees sheet 3 so thres no harm. i cant just run the code in sheet 1 because those cells have the formula

=’Data Entry’!I3 & CHAR(10) & ‘Data Entry’!J3 & CHAR(10) & ‘Data Entry’!K3
it appears VB wont run the code because the cell refernces take presedent over coding format, is that true? either way when the code gets entered into sheet 3 and i try to copy and paste the info from sheet 2 the line

strCell = Target.Value

gives me an error.

What am i doing wrong????

Reply | Quote

That’s impossible to tell without seeing the rest of your code. Copy and paste the entire procedure/function.

Reply | Quote

The code posted was really only intended for cell by cell data entry. I suspect in your case you are using a formula and copying/dragging it down. The code as posted can only handle changes made to a single cell at a time, so if you edit more than one cell at a time you will get errors.

If you are using formulae and not accepting direct data entry, then the formula could (hopefully) include the functionality of the code and you may not need to code.

If you could post a sample of you workbook with just enough dummy data included I am sure somebody could provide you with a formula, or suggest an alternate approach.

Andrew C

Reply | Quote

Here you go, more details are on the INFO tab. thanks.
Ed

Reply | Quote

There are numerous problems with using the Change event to accomplish what you are trying to do. First, the cell contains a formula, and the change event is not going to trigger when the cell changes because the formula recalculated. Second, your code would replace the formula with a constant and any changes to the source cells after that would not show up on the clean up sheet. Third, the code in your vba routine will not properly handle the names that are combined in the cell.

There are a couple of solutions to this problem. I have attached a modified version of your worksheet that I think does what you want with a formula in cell D1 on the clean up sheet without using the change event code. If this does not work, then you could put code in the worksheet change event for the data entry sheet. This code would look for changes in any of the columns where the names are entered. If any cell in these columns is changed, it would then loop through all of the name cells in that row and create a string that contained the concatinated names in the format that you want on the cleanup sheet and then put the result in the appropriate cell on that sheet.

Reply | Quote

Does the job perfecty but i get the #VALUE error in all cells that do not have dat on DATA ENTRY SHEET. I tried an IF ISERROR but i couldn’t get itt o work. That was something i learned here. Am i trying the right thing? Is there a way to not display the # VALUE? So close i can smell it….
ED

Reply | Quote

Try the attached.

Reply | Quote

WORKS GREAT!!!! can’t thank enough.

Reply | Quote