If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|