There will definitely be a better way with power query, where you can split on a number, but in the meantime, this seems to work on the small data set provided.

=TRIM(MID(A2,1,MATCH(1,--ISNUMBER(MID(A2,ROW($A$1:INDIRECT("$A$"&$B$1)),1)+0),0)-1))

In cell B1 we have the max length of the text in column A, so i can create an array of numbers 1 to the max length. This goes inside the second MID to check if each character is a number, match then finds the first number.

So, we then have the first mid which is basically the text in A2 starting at the first character and extracting up to the character before the first number.

Trim just removes the spaces

If there are any cities that contain a number, it won’t work!

You need to test it on a much larger dataset

Hi m r,

Attached is a solution using Power Query.  I also did one with formulae, different to Purfleet.  You can't have too many options.

To extract the city

=TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE($A1,"Hourly ",""), "Salary ",""), " CSV-PAY SUMMARY RUN.csv",""), 1,FIND(C1,A1)-2))

and the date

=TRIM(RIGHT(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE ($A1,"Hourly ",""), "Salary ",""), " CSV-PAY SUMMARY RUN.csv", ""),8))

These only work with data as provided in your example.  If your complete data is different then adjustments will need to be made.

Regards

Phil

Thanks guys! Really appreciate. I’ll try to add these formulas in my dataset. However, I too came up with another solution

For City: I used search function to find hypen and subtracted 2 from that and nested that in Left function to extract city names. And similar with slightly different search function to get dates and extracted using Right function. Only catch is if there’s another word in between city names and date then that too gets extracted, but i can add one more column and separate those. Thats and much appreciated!