Replacing words in a sentence in an Excel Cell with abbreviations

[kingpnp]

How could I replace words in a sentence in an Excel Cell with abbreviations.

ex:

Cell A1 = California license plates are nicer than a Wyoming plate.

Abbreviation list:

California | CA
Wyoming | WY

I would like to make a new cell show the corrected sentence as:

CA license plates are nicer than a WY plate.

Can this be done with a formula(s) and no VBA? If so, how?

[FoxSeaLady]

Hi

Use find and select on the home menu to replace "California with CA then replace Wyoming as WY. I assume you need to change many cells with several alternative spelling. To make several changes select more cells.
Hope this helps

Cheers

[kingpnp]

Hi

Use find and select on the home menu to replace "California with CA then replace Wyoming as WY. I assume you need to change many cells with several alternative spelling. To make several changes select more cells.
Hope this helps

Cheers

Thanks for the quick reply. I should've put this in my original post. I know how to use CTRL+F Find and replace. this is not going to be for a one off scenario. I need to have it so that it translates any sentence i copy and paste into say Cell A1, and makes the sentence show the abbreviations for certain words in lets say Cell B1. Which is why I need a formula of some type.

[AlKey]

I think this can be done with formulas but you need a list of states. You can copy states list from this link
http://www.softschools.com/social_st...abbreviations/

Enter first formula in B2 and drag formula across to cell C2 and drag down.

Please Login or Register  to view this content.

Enter the second formula in D2 and drag formula across to cell E2 and drag down

Please Login or Register  to view this content.

Please see attached file

[FlameRetired]

Try the attached. With sentence in A1, a lookup table of states and abbreviations in K:L enter this formula in B1 and fill across until replacements are completed.

Please Login or Register  to view this content.

[FlameRetired]

Actually you can enter this in B1 and fill across until you get blanks.

=LOOKUP("zzzzzz",CHOOSE({1,2},"",SUBSTITUTE(A1,LOOKUP(1E+306,FIND($K$1:$K$50,A1),$K$1:$K$50),LOOKUP(1E+306,FIND($K$1:$K$50,A1),$L$1:$L$50))))

[kingpnp]

Try the attached. With sentence in A1, a lookup table of states and abbreviations in K:L enter this formula in B1 and fill across until replacements are completed.

Please Login or Register  to view this content.

Thats it. It was Substitute. That's what I was missing. Question, how is it that the formula knows to change from A1 to say A3 if I delete that first A1 cell and type a sentence in A3? Thank you.

Wait I spoke to soon. I've noticed it seems to only be able to take 2 words and abbreviate those 2 at a time. anymore than that, and it will not abbreviate the first word.

For example. I type: California, Texas . It responds: CA, TX (which is correct)

now if i type: California, Texas, New Hampshire It responds: California, TX, NH (which is incorrect since California is not abbreviated.)

I need something where it wont matter how many states i want to use in my sentence in order for it to abbreviate them correctly.

Is that possible with a formula?

Or is that your point on your last reply where you say drag to the right until blank. OR I would need to drag so far to the right to the point of me having as many formula cells as I do words that could possibly be abbreviated(from my abbreviation list/legend)?

[FlameRetired]

Or is that your point on your last reply where you say drag to the right until blank. OR I would need to drag so far to the right to the point of me having as many formula cells as I do words that could possibly be abbreviated(from my abbreviation list/legend)?

Yes. If you drag the formula far enough to the right it will complete the job.

If you think that approach might exhaust the list or if you prefer a single formula that will likely require some VBA ... a user defined function.

BTW: "California, Texas, New Hampshire" worked at my end.