Help Needed

Question

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?

riny_van_eekelen · Answer

student1231&nbsp;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.

student1231 · Answer

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

riny_van_eekelen · Answer

student1231&nbsp;I guess so. What Excel version are you using?

student1231 · Answer

Version 16.83

riny_van_eekelen · Answer

student1231&nbsp;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.

Forum Discussion

Help Needed

Share

Resources