General Text Conversion

Topic

New Reply

The attached workbook represents an export of data that I receive to compile personnel travel reporting. I actually receive two different exports both in MS Excel. The first export is a current travel report while the second is a future travel report that has names and travel itineraries for personnel traveling some time in the future (starting tomorrow perhaps). Neither export has unique identifiers other than names. I need to merge the two exports to build my reporting off of, but the data needs to be fixed first.

Problem number one is cell B2. Texas(SAN ANTONIO) I would like the names of States and Cities to be CAPITALIZED. Is there a function for doing that?

The next problem is doing something useful with the date text string in C2. 03-OCT-10 – 02-NOV-10. I can break it up using text to columns, but then formatting is not as smooth as I want. What is the optimal way of separating the data into a start and end date column in a proper date format that can be sorted and then rejoined again in different string?

Your help is always appreciated.

Amy

Reply | Quote

Replies

Hello Amy – Nothing was attached to your message.

Reply | Quote

The attached might offer some formula suggestions that may work for you.

Reply | Quote

Name:
=PROPER(A2)

State/City:
=PROPER(B2)

StartDate [Format as desired]:
=DATEVALUE(LEFT(C2,9))

EndDate [Format as desired]:
=DATEVALUE(RIGHT(C2,9))

Steve

Reply | Quote

Peter/Steve,
Thank you. I integrated some changes with what you provided. I am trying to separate the city out of the parenthesis as another transformation step. I got close, but take a look at column F in the attached workbook.

Thanks for you help.

Amy

Reply | Quote

How about this for column F

=SUBSTITUTE(MID(B2,FIND(“(“,B2,1)+1,LEN(B2)),”)”,””)

Reply | Quote

Peter,
That worked brilliantly. I think my pieces are coming together and I will be able to move this solution to the MS Access platform.

You guys are great.

Amy

Reply | Quote