Splitting an alpha numeric string

[ECB]

Hi there,

Regards to all, and I hope you are all staying safe in these mad times.

I have a question about splitting an alphanumeric string that I think I've got clear in my head, I know there will be a simple solution, but I just can't grasp it.

I have an alphanumeric string which consists of one/two letters, following by one/two numbers sometimes followed by a space, followed by a single number followed by two letters.

That is the string could be any of the following:

G2 1AA (which might also be written as G21AA)
G32 1AA (which might also be written as G321AA)
GA2 1AA (which might also be written as GA21AA)
GA32 1AA (which might also be written as GA321AA)

Now I want to split the first part (the single or double letter, and the single or double number) from the second part which is always a single number followed by two letters. I'd like a formula that works for all combinations of the string.

I might have overthought this, as actually all I want to do is take the last 3 letters of the string for the second part, and trim the last 3 letters for the first part - the trim function would strip out spaces.

Am I right?

 

[ECB]

I wish I'd come here ages ago, as just typing it out I think I've got it.

If A1 is the cell with the string then:

=TRIM(LEFT(A1,LEN(A1)-3)) for first bit and

=RIGHT(A1,3)

for second bit.

Thanks for reading, and the inspiration!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
2	SK176RU	SK17	6RU
3	SK178DG	SK17	8DG
4	SK2 5TN	SK2	5TN
5	S2 7NF	S2	7NF
6	EX31 4PN	EX31	4PN
7	B96PB	B9	6PB
Result

Cell Formulas
Range	Formula
B2:B7	B2	=TRIM(LEFT(A2,LEN(A2)-3))
C2:C7	C2	=RIGHT(A2,3)

 

[Fluff]

I see you beat it.

 

[alansidman]

Using Power Query, here is the Mcode. A simple split based upon the three right places.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 3}, true), {"Column1.1", "Column1.2"})
in
    #"Split Column by Position"

Book1
	A	B
1	Column1.1	Column1.2
2	G2	1AA
3	G32	1AA
4	GA2	1AA
5	GA32	1AA
Sheet2

 

[ECB]

Thanks for the responses - much appreciated. This is the best forum on tinternet.

 

[Fluff]

Glad we could help & thanks for the feedback