Changing Text from all CAPS

Topic

New Reply

I have a database file in MYOB in which the majority of information is in CAPS – ie. NAME AND ADDRESS are all caps!

Is there a way of importing into Excel or Access and removing caps and replacing with lower case.

Of course just to make it easy I also require first letter to be a Capital.

I have a sign making graphics program that can do it but can’t get the data in there.

Reply | Quote

Replies

In an empty column, in the same row as the first row with the all caps, enter the formula:

=Proper(A1)

Where A1 is replaced with the cell where the all caps data is located. This should give you the data with the first letter of each word capitalized. Copy this formula down the column for as far as the data goes. This column should now have the correct case. Now, select all of the cells with the formula and do an Edit/Copy. Now, select the top cell with the original data and do Edit/”Paste Special.” In the dialog box select the radio button next to “Values” in the “Paste” section and press OK. The original cells should now contain the strings with the case you want, and you can delete the formulas.

Reply | Quote

In addition to Legare’s lesson on Proper functions (thanks), the way I approached this one time used Word.
1. from excel, copy the cells you want to convert to the clipboard.
2. paste them into Word; this will create a table with the number of rows and columns the same as your Excel selection
3. select the entire table.
4. either choose a or b depending on your rqmt for conversion:
a. use SHIFT+F3 to cycle thru a limited number of conversions
b. select Format | Change Case which will give you a larger set of conversions; choose the one you want and click OK
I think either of these give a little more flexibility than Excel’s PROPER function.
5. with the Word table still selected copy to the clipboard
6. back in Excel, select the upper left cell of your original selection and paste the clipboard. this will replace the entire range with the “table” from Word, which just happens to be the same size as your original selection from step 1.

proper use of proper tools – of course, if you don’t have Word…

Fred

Reply | Quote

Select the cells you want to convert to Proper then run this macro:

Sub Proper()

For Each cel In Selection
gg = cel.Value
cel.Value = “=Proper(“”” & gg & “””)”

Next cel

End Sub

Reply | Quote

Thanks one and all – I used Michaels answer and it worked a treat – the database looks great now except for Some updates within the program MYOB have added extra fields into them. ie address now has 4 fields instead of 2. How can I get an excel macro to read a cell and if it meets certain criteria to take that information and place it into another cell eg if the cell has West Perth Wa 6005 in it, I would like the West Perth into one cell, Wa to be WA in another cell with 6005 in yet another cell.
Are there any basic macro books out there that an Idiot could read?

Reply | Quote

Assuming your fields are separated by spaces, this macro will do what you want. First make sure you have enough blank columns to receive the parsed addresses, then select all the addresses, then run the macro.

After you tell it how many fields you want for the State, postcode, etc., it starts at the end of each address and runs backwards until it reaches a space, puts this string in a new cell, then repeats the process until it has filled the number of fields chosen. It then puts whatever is left in the column next to the unparsed addresses.

Sub AddressParse()
‘
Do Until NumFields >= 1
NumFields = (InputBox(“How many fields AFTER the city name?”))
Loop

Set selrange = Selection
For Each Cel In selrange

gg = Cel.Value
Cel.Value = “=Trim(“”” & gg & “””)” ‘removes excess spaces

m = Val(NumFields)
p = m + 1
sl = Len(Cel.Value)
For n = sl To 1 Step -1
r = sl – n

If Mid(Cel.Value, n, 1) = ” ” Then
Cel.offset(0, p).Value = Mid(Cel.Value, n + 1, sl – n)
sl = n – 1
m = m – 1
p = p – 1
If m = 0 Or n = 1 Then
Cel.offset(0, p).Value = Mid(Cel.Value, 1, n – 1)
n = 1
End If
End If

Next n

Next Cel
End Sub

Reply | Quote