Formula help

[George Gee]

I have a column of map grid references with the structure: SD642522

How would I go about changing them to: SD 64200 52200


Many thanks

George Gee

 

[Joe User]

I have a column of map grid references with the structure: SD642522
How would I go about changing them to: SD 64200 52200

If the grid ref is always 2 letters and 6 digits:

1. The first part can be isolated with LEFT(A1,2).
2. The middle part with MID(A1,3,3).
3. The last part with RIGHT(A1,3).

If you want them in separate cells, then use =LEFT(A1,2) etc.

Alternatively, you could use the Text To Columns wizard (Data > Text to
Columns.

If you want them in one cell, then:

=LEFT(A1,2) & " " & MID(A1,3,3) & " " & RIGHT(A1,3)

If you want that to replace the original grid ref, then copy the cell with
the above formula and paste-special-value into A1.

 

[George Gee]

If the grid ref is always 2 letters and 6 digits:

1. The first part can be isolated with LEFT(A1,2).
2. The middle part with MID(A1,3,3).
3. The last part with RIGHT(A1,3).

If you want them in separate cells, then use =LEFT(A1,2) etc.

Alternatively, you could use the Text To Columns wizard (Data > Text
to Columns.

If you want them in one cell, then:

=LEFT(A1,2) & " " & MID(A1,3,3) & " " & RIGHT(A1,3)

If you want that to replace the original grid ref, then copy the cell
with the above formula and paste-special-value into A1.

OK, thanks for the pointers, I needed the trailing "00"s, so used this:
=LEFT(A1,2) & " " & MID(A1,3,3) & "00 " & RIGHT(A1,3) &"00"

Cheers
George Gee