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
|