splitting alpha and numeric data in string 5

[lindercoop]

Hello -
I am working in Access 2007. I have a string field with varying alpha/numeric combinations like:

E123456
BDI14222
RA9871
LIN4420

I need to end up with 3 fields something like this:

E123456 E 123456
BDI14222 BDI 14222
RA9871 RA 9871
LIN4420 LIN 4420

Basically want to de-concatenate based on letters vs. numbers.

I appreciate any help I can get.
Thanks

 

[SkipVought]

..and yon Cassius has a lean and hungry look!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

OK, seriously, I don't know what we're talking about here. Have I offended in some way?

SkipVought doth bestride the narrow world Like a Colossus, but I surely wouldn't want to stab the man.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

And you TOO, have replied unwittingly in the wrong genre?

I was commiserating. "I feel your pain!"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Ah. Thanks for the clarification.



I wanted to make sure I wasn't being accused of stabbing anyone in the back!

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

No long knives. Just a simile, once removed or a morphed metaphor.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...and inhale oxygen smoothly and expel carbon dioxide effortlessly, my cohort.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Actually, I think Skip was making reference/fun of the fact that I often make fun of him being Excel-centric.

anotherhiggins: "I was, of course, thinking about Excel."

Skip: "Et tu Brute? "

Gerry

 

[SkipVought]

My wife thinks that if you drop the cel, you would be closer to the truth.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

anotherhiggins, I'm grateful: I also failed to notice the Access reference until after I'd written an Excel formula approach. I noticed just before hitting "Submit Post". But I'm grateful to you, as your Excel formula was much, much more elegant than mine.

So if I ever need to do it for real in Ex(cel), now I've learnt a better method.

 

[AnotherHiggins]

Thanks, lionelhill! I'm glad someone found it useful.



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

BTW, John, VERY nice solution! ==> [purple]*[/purple]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]