Forum Discussion
If column has a word within it pull the name in another column
The file you sent is my first suggestion where all data must be entered. Attached is the file with the changed M code which accepts rows without a city. I changed the name of the file from "If column has a word within it pull the name in another column" to "CHANGED If column has a word within it pull the name in another column" for easier identification.
Another alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can as well place the green result table in another worksheet.
[Expression.Error] we cannot convert the value null to type Text
The expression error occurs if no city is entered for a first and last name.
If it should be possible to enter first and last name without at least one city then the query must be changed accordingly. For example with a changed query the result could look like in the screenshot below where the first and last name is entered under "no city entered" if there isn't an entry for city in the blue table.
Can you attach your file without sensitive data? Another strange thing in your file is that the M code returns "Adam Example" while "Ann" and "Example" was entered. In the last three rows of the blue table there is no data entered. Without seeing your file and code i can't help you i'm afraid. Alternatively you can enter the data from your file in the file i already sent you and run the query.