Functions or VBA script to split one column in two based on content-CodePudding

what would be the easiest way to do this?

The data has thousands of rows.

The data in M column is merged by data provider. I need this data split into separate columns for import.

Criteria to split:

Column one, named "R": The capital letter values are either either a.) R or b.) RS There are no blanks
Column two, named "Guidance mark": Values to split are values in the "exponent" after either R/RS. These can be values or blanks

Merged

Desired

Tried googling a lot of possible solutions, however didn't manage to find how to split this

CodePudding user response：

You can use this formula:

=LET(d,A1:A4,
R,IF(LEN(d)>2,LEFT(d,LEN(d)-4),d),
gm,IF(LEN(d)>2,RIGHT(d,4),""),
HSTACK(R,gm))

It handles first the R- column retrieving the first letters except of the last 4.

Then the retrieve the last four characters for guidance mark.

Finally puttin them into a new array (HSTACK)