Formula to copy and paste the only occurence of 2 characters together

[duugg]

I would like a Formula to copy and paste the only occurence of 2 characters together. Heres an example

In cell B2 I have

Baltimore, MD - NPB

I would like to paste just the MD in cell D2.

In other words, I just want to extract the state and put the state into cell D2.

Thanks much in advance.

[Carim]

Hi,

Following should be OK ...provided your data structure is consistent ...

=MID(B2,FIND(",",B2,1)+2,2)

HTH
Carim

[MDubbelboer]

I would like a Formula to copy and paste the only occurence of 2 characters together. Heres an example

In cell B2 I have

Baltimore, MD - NPB

I would like to paste just the MD in cell D2.

In other words, I just want to extract the state and put the state into cell D2.

Thanks much in advance.

is it always after the ","?
*edit* damns yous carim!! lol

=MID(B2,FIND(",",B2,1)+2,2)

if not it'll be a bit uglier

[duugg]

No, data can be different, sometimes comma space state, sometimes comma state. For example, I tried that formula on another one. Here it is.


Duluth,MN-CA

result N-

Any suggestions?

[Carim]

... manual adjustments ... depending on how many lines you are dealing with ...
unless you can come up with a limited number of different types ...

Carim

[MDubbelboer]

No, data can be different, sometimes comma space state, sometimes comma state. For example, I tried that formula on another one. Here it is.


Duluth,MN-CA

result N-

Any suggestions?

i think you're going to have to go the macro route.
that duluth,MN-CA is also gonna give a bit of a problem since MN and CA both are two characters together....sorry i'm not from the states what is the CA representing?

[duugg]

The CA is a coding system I use for a specific location in MN. I should've noted that I only want the 2 characters after the comma and not any other 2 characters.

[Carim]

Hi,

If your whole data has the coherence you are describing ...

=TRIM(MID(B2,FIND(",",B2,1)+1,FIND("-",B2,1)-FIND(",",B2,1)-1))

For these problems, consistency of data structure is key ...

HTH
Carim