text to column in Access

[aug_aug]

I need to split data in the form of:

24N15E12 into 24 N 15 E 12

where the spaces delimit columns, but it could also be:

24N3W2 into 24 N 3 W 2

I was trying to use the text to columns in Excel, but that's not quite it, I
was hoping I could write a query or script that goes something like:

select characters until the 1st letter (N) > copy letter and any preceding
numbers > make new column > select until next letter (E or W) > copy 2nd
letter and any preceding numbers > make additional new column > finally, make
a new column out of remaining numbers

Excuse this mess of a question, I (if you can't tell) have little to no
experience with databases or scripting, but feel I could give it a shot with
a nudge in the right direction (i.e., is this a VBA thing, macro, just use
Access, etc.).

Thanks in advance for any advice.

 

[Jeff Boyce]

How many?

(no, seriously, the amount of time and effort to create, debug, test and
implement a routine may exceed the time and effort to just do this
'manually')

Unless, of course, this is a 'teaching moment' ...<g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

Here is a start using field [Style] and assuming that these are coordinates
and therefore only N or S and E or W.
Expr1:
IIf(InStr([Style],"N")>0,Left([Style],InStr([Style],"N")-1),Left([Style],InStr([Style],"S")-1))
Expr2: IIf(InStr([Style],"N")>0,"N","S")
Expr3: IIf(InStr([Style],"N")>0 And
InStr([Style],"E")>0,Mid([Style],InStr([Style],"N")+1,InStr([Style],"E")-InStr([Style],"N")-1))

Expr3 is only 1/4 done.

Gotta go.

 

[aug_aug]

About 20k entries, and yes - consider this a teaching moment, please talk to
me like I'm 6, I'm not even sure where to enter the info provided by Karl
above.

Thanks!

 

[KARL DEWEY]

Create a select query using your table that has the field that is to be
parsed and open in design view.
Paste the items below into an empty space on the FIELD row of the grid.
Edit to change 'Style' to the name of your field.

Latitude: Str(Val([Style]))

Northing: IIf(InStr([Style],"N")>0,"N","S")

Easting:
Str(Val(Mid([Style],IIf(InStr([Style],"S")>0,InStr([Style],"S"),InStr([Style],"N"))+1,(IIf(InStr([Style],"E")>0,InStr([Style],"E"),InStr([Style],"W"))-IIf(InStr([Style],"N")>0,InStr([Style],"N"),InStr([Style],"S")+1)))))

Longitude: IIf(InStr([Style],"E")>0,"E","W")

Altitude:
Str(Val(Right([Style],Len([Style])-IIf(InStr([Style],"E")>0,InStr([Style],"E"),InStr([Style],"W")))))