Capitalization of Abbreviated states

[nonie123]

I'm doing a mail merge from an address spreadsheet. The spreadsheet originally capitalized all the letters. I converted the spreadsheet to sentence case. Once I did that, the state abbreviation became Tx instead of TX which is what I want. The city, state and zip are in the same cell EX. Rockwall, TX 75000 is what I want but what I'm getting is Rockwall, Tx, 75000. How can I change this to TX?

[RusselB]

While your post is in the Writer portion of the forum, you state that you are using a spreadsheet.
Is the problem in the spreadsheet or does it appear after you import the data into Writer?
If the problem is in the spreadsheet, then a couple of helper columns, with the right formulas, should handle the problem... and I can help with that.
If the problem doesn't appear until the data is imported into Writer, then someone else will have to help as my knowledge of Writer is limited.

If the problem is in Calc (the spreadsheet), then use two empty columns.
With your City, State Zipcode in A1, and using columns B and C for your helpers, in B1 enter

Code: Select all

=MID(A1;FIND(", ";A1;1)+2;2)

and in C1 enter

Code: Select all

=SUBSTITUTE(A1;B1;UPPER(B1))

You will have to adjust the cell addresses for your spreadsheet,

[nonie123]

You're correct this is in Calc. I'm going to try this now and report back.

[nonie123]

My column is J and my data starts at J2. J1 says City, State, Zip. How does this change? Where should I place the cursor? I added columns K and L.

I put my cursor in K1 and added =MID(J1;FIND(", ";J1;1)+2;2) I received Err 509

=SUBSTITUTW(J1;K1;UPPER(K1)) I received NO

What I'm I doing wrong?

[RusselB]

Just change the row number so that it's relative to the row number where your data actually starts.
Since your data starts in J2, then change J1 in your modified formula to J2. Likewise change J1 to J2 and K1 to K2 in the SUBSTITUTE formula.

[RusselB]

A couple of things to note:
The first parameter of the FIND function has a space after the comma.
The 2nd formula uses the SUBSTITUTE function, in your reply you entered SUBSTITUTW. I hope that was just a typo.

[nonie123]

Yes, that was a typo. It wasn't a cut and paste. The system and I wouldn't allow cut and paste.

Any other spaces.

[RusselB]

No. Just wanted to make sure the FIND function was using the same search parameter that I tested with,
.