Validating a cell value excel macro

09 Jul

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.

Special Cells(xl Last Cell) ' the last cell on the worksheet Set o Target = o Target. Advanced Filter Action:=xl Filter Copy, Copy To Range:=o Target, Unique:=True ' Get the number of rows including the first row which is the header numrows = Worksheet Function. Entire Column) ' create an 2-dim array of the rows vs1 = o Target.

Public Function Count Unique(rng As Range) As Integer Dim dict As Dictionary Dim cell As Range Set dict = New Dictionary For Each cell In rng. Dim o Target As Range Dim r As Long, numrows As Long Dim vs1, vs2 As Variant ' Get the first unused cell on the first row where the unique vaues will be temporarily populated Set o Target = o Range. Column 1) ' the first unused cell on the first row ' Copy the unique values from your o Range to the first unused cell on row 1 o Range.

And, you can also use the keyboard shortcut “ALT D E” (Learn more Keyboard Shortcuts) to access the “Text to Column” wizard.

Select the range from A9 to A17 and then press “ALT D E”.

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.Sometimes, instead of spaces, comma is used for separations.Moving on to the first example, as you can see there are a few names in “column A” that we want to extract and return in column B, C and, D as first name, middle initial and last name respectively. It's also one of the most compact solutions I've seen. There are a few things that could cause this to not work. Another example of where this might not work is if you have one cell with the value 31 and another cell with a text value of "31". You could try this: It works for both numbers and text, it handles blank cells, it handles errors in referenced cells, and it works in VBA.