Formula help
 

I have a column of map grid references with the structu SD642522

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


Many thanks

George Gee

Formula help
 

"George Gee" wrote:
I have a column of map grid references with the structu 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.

Formula help
 

On 14/03/2010 20:43, Joe User wrote:
"George Gee" wrote:
I have a column of map grid references with the structu 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.


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