Separate the number into a column and remove the yes no

[philky001]

I have an excel sheet with only one column like this:
Col A
1201NSNYC-Brooklyn Group HHCyes
1202NSNYC-Brooklyn Office HHCCyes

I would like to run a formula on the dataset that would separate for me the 1201 all rows start out with such a number.
So that the new column would contain 1201 and it would delete itself from the original col. also if we can delete the yes at the end (sometimes it is no ) but mostly is yes so it can
be done just for yes. Thank you for your help.
The result after the formula runs is:

Col A Col B
1201 NSNYC-Brooklyn Group HHC
1202 1202NSNYC-Brooklyn Office HHCC

 

[Joe4]

Col B is fine. The only issue is C remains as a formula so that if I delete B i don't have the data. But this has been very helpful, I can manage at this point. Thank you kindly.

After you apply the formulas, you can easily/qyuckly turn them into hard-coded values by copying and pasting over them with Copy --> Paste Special --> Values.

Otherwise, you would need to use VBA to convert them to hard-coded values all in one step (you could just turn on your Macro Recorder and record yourself performing these steps manually to get most of the VBA code that you need).