Help Needed

[student1231]

I am looking for help with extracting certain parts of a cell. Some example cells appear as the following:

AUS177-4M

Canada551-3W

ENG342-10Mo

The first set of text is a location. The first set of numbers is an employee ID. The numbers after the hyphen are a plant ID. The letters after the hyphen are a pay period. What is the best formula to go about extracting each of these separately, due to the variable lengths of each category?

[#2]

@student1231 I prefer to perform such tasks with Power Query. It's no more than two steps of splitting. The first split is by the "-". The second split is where the text in the now 2nd column changes from a digit (i.e. a number) to a non-digit (i.e. a letter).

See attached.

[#3]

@Riny_van_Eekelen 

Thank you for the help. Is there a way to do it with formulas without the query?

[#4]

@student1231 I guess so. What Excel version are you using?

[#5]

Version 16.83

[#6]

@student1231 OK, you're on a Mac and a modern version.

I quickly cooked up a formula based solution that should work for you as well. It's not great and probably needs improvement. That's why I prefer PQ for this. So much easier and neater, despite the fact that you need to do a refresh when the underlying data changes.