Is there an easier way to flip FirstName LastName to LastName,First Name besides running a macro? There must be a simpler formula – any advice?

via Is there an easier way to flip FirstName LastName to LastName,First Name besides running a macro? There must be a simpler formula – any advice? | LinkedIn.

Try this

=MID(A1,SEARCH(” “, A1)+1,99)&”, “&LEFT(A1,SEARCH(” “,A1)-1)

I was asked the following question by a colleague today.

I have two spreadsheets and I have to check which part number is listed on both. What’s the quickest way apart from: copy party number go onto other spreadsheet do CTRL find and paste part number and do find all?

I recommended the Match Function.

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

Lookup_value is the value you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.

Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.

If match_type is omitted, it is assumed to be 1.
Remarks

I cobbled this code together for someone at work. They wanted to unhide rows if some cells contained Yes.

The control cells were D4, D5 & D6. You should be able to reuse this code.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub