Now you can see that the names in “column A” have been extracted in multiple columns.But, if you look closely you will notice that in the 11 row, “Steve Smith” does not have a middle initial and his last name is moved to the middle name column. Value) Then c = c - 1 Exit For End If Next j Next i ' c now equals the unique item count put in the 12'th row Sheet1. Value = c End Sub Here is a VBA function that works for me. You'll need to reference 'Microsoft Scripting Runtime' and prefix the Public Function Count Unique(rng As Range) As Long Dim dict As Scripting. If you want errors completely excluded, it would need to be modified for that.
This is a very common problem for any excel user, and you too must have come across it.
This is a very common distress for a lot of Excel users but there is no specific in-built function in Excel that can extract first name, middle initial and last name from a cell that contains a complete name.
It requires a reference to the Microsoft Scripting Library, for the dictionary object. ' You can ignore this element unless the first row in your o Range is a unique value ' in which case the header will be that value.
You can use it as a worksheet function, referencing any range, eg “=Count Unique(N8: O9)” It handles text and numeric values, and treats blank cells as one value It does not require dealing with array functions. Dictionary Dim cell As Range Set dict = New Scripting. In my tests, this one from Jacob above only works for numbers, not text, and does not handle errors in referenced cells (returns an error if any of the referenced cells returns an error): Public Function Unique Values(o Range As Range) As Variant ' Uses the built-in Advanced Filter Excel command to return the unique values onto the Worksheet ' and then populate and retuns an array of unique values ' Note: The index:0 element in the returned array will be the header row.
Value = count End Sub may also write a VBA macro (not sure if that's what you're after though.) Something to the effect of (given a spreadsheet with A1-A11 filled and B1-B11 empty): Sub Count Unique() Dim count As Integer Dim i, c, j As Integer c = 0 count = 0 For i = 1 To 11 Sheet1. Column a = Get Letter From Number(a) last Row = Range(a & Rows. End(xl Up)Count = 0 For Each c In Range(Range(a & "1"), Range(a & Rows. End(xl Up)) If = 1 Then Re Dim Values(last Row) Values(Count) = c.
Resize(numrows) ' Prepare a second 1-dim array for the result Re Dim vs2(numrows) ' Transfer the 2-dim array into the 1-dim array For r = 1 To UBound(vs1, 1) vs2(r - 1) = vs1(r, 1) Next ' Return the 1-dim array as the function result Unique Values = vs2 ' Clean up the extra column on the worksheet o Target. Delete End Function Sub Count Unique() Dim Count, x, a, last Row, Values(), String Values a = Active Cell.
And, now we need to follow 3 easy steps, and as you can see on top of the wizard box, we are at the first step.
step of this wizard, and as you can see below, here we need to set the delimiters which our data contains.
Here you can see few “Delimiters” checkboxes; so, either you can select these check boxes or can mention the delimiter in “Other” checkbox.