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

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.

Hi,

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

Please Login or Register  to view this content.

HTH
Carim

Originally Posted by 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.

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

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?

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

Carim

Originally Posted by 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?

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?

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.

Hi,

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

Please Login or Register  to view this content.

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

HTH
Carim

Carim,

Thanks much! That worked great! Can you tell me the formula to extract all data up to but not including the first comma in a cell?

For example, in C2, I have the following data

Anchorage,AK-SSA

I would like a formula to show just

Anchorage

in D2.

Thanks again

You 'd rather have me do it ...
despite the fact you now have all the elements to be totally independent ...
lol ... !!!

Please Login or Register  to view this content.

HTH
Carim

Thanks, that worked too. I don't really know how to program yet, I want to learn though! If I did know even the basics, I'd know how to manipulate the formula you gave me the first time, but I really don't.

Like "Mid" for example, I assume that Mid means middle of the cell?

Whatever it is thanks again for the help.

Yes ...
Mid() stands for middle ...

But Try, Test, read Help , ask questions to the forum ...
and you will soon turn yourself into an expert ... !!!

HTH
Carim

Okay,

Thanks again everyone! I'm sure I'll have another one soon.