Capitalization of Abbreviated states

Discuss the spreadsheet application

Capitalization of Abbreviated states

Postby nonie123 » Thu Jul 12, 2018 8:16 pm

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?
Last edited by nonie123 on Thu Jul 12, 2018 11:20 pm, edited 1 time in total.
openoffice 4.3.1 on Windows 10
nonie123
 
Posts: 5
Joined: Thu Jul 12, 2018 8:03 pm

Re: Capitalization of Abbreviated states

Postby RusselB » Thu Jul 12, 2018 10:38 pm

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   Expand viewCollapse view
=MID(A1;FIND(", ";A1;1)+2;2)
and in C1 enter
Code: Select all   Expand viewCollapse view
=SUBSTITUTE(A1;B1;UPPER(B1))

You will have to adjust the cell addresses for your spreadsheet,
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4412
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Capitalization of Abbreviated states

Postby nonie123 » Thu Jul 12, 2018 10:45 pm

You're correct this is in Calc. I'm going to try this now and report back.
openoffice 4.3.1 on Windows 10
nonie123
 
Posts: 5
Joined: Thu Jul 12, 2018 8:03 pm

Re: Capitalization of Abbreviated states

Postby nonie123 » Thu Jul 12, 2018 11:17 pm

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?
openoffice 4.3.1 on Windows 10
nonie123
 
Posts: 5
Joined: Thu Jul 12, 2018 8:03 pm

Re: Capitalization of Abbreviated states

Postby RusselB » Thu Jul 12, 2018 11:44 pm

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.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4412
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Capitalization of Abbreviated states

Postby RusselB » Fri Jul 13, 2018 12:52 am

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.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4412
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Capitalization of Abbreviated states

Postby nonie123 » Fri Jul 13, 2018 1:00 am

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.
openoffice 4.3.1 on Windows 10
nonie123
 
Posts: 5
Joined: Thu Jul 12, 2018 8:03 pm

Re: Capitalization of Abbreviated states

Postby RusselB » Fri Jul 13, 2018 1:06 am

No. Just wanted to make sure the FIND function was using the same search parameter that I tested with,
.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4412
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 28 guests