Find common starting string in all rows in column

[stranula]

Hi everyone,

I'm working on a VBA script, and part of it requires renaming potentially up to 40 names. The way the data is originally exported will be consistent between each row in any given dataset, but not between datasets. I am looking for a VBA code that will look at all strings in A1:A41, and return the starting common string.

For example, dataset 1 may be
A1: Demo a_b 2021.05.19 - Mark - Monday
A2: Demo a_b 2021.05.19 - Jared - Tuesday
A3: Demo a_b 2021.05.19 - Patty - Thursday
A4 - A41 same pattern

and dataset 2 may be
A1: Demo a_b 2021.05.20 - Mark - Monday
A2: Demo a_b 2021.05.20 - Jared - Tuesday
A3: Demo a_b 2021.05.20 - Patty - Thursday
A4 - A41 same pattern

So as you can see, each time the data is analyzed, the first part is consistent within that dataset, but the second part changes. I am looking for a code that will search A1:A41 and return as much of the string from the left that matches every cell.

The ultimate goal is the return a prompt which says "All of your samples start with XXX, would you like me to remove this from their names?"