Transform text in OpenOffice.org Spreadsheet

Question

I have list of text that contain double quoate and upderscore. How can I use function to change text pattern to following format.

"CUSTOMER_DETAIL"  to CustomerDetail
"PERSON" to Person
"CUSTOMER_ADDRESS_DETAIL" to CustomerAddressDetail

Thank you for replay.

Answer 1

Use SUBSTITUTE to replace "_" with space and PROPER to capitalize first letters. Use MID to get text inside quotes. At the end use SUBSTITUTE to strip spaces.

=SUBSTITUTE(PROPER(SUBSTITUTE(MID(A1;2;LEN(A1)-2);"_";" "));" ";"")