I'm fairly new to functions as need help. I have a workeeet that looks like
this
A B C D
1 Fname Lname HomeStreet State
2 David HEIDENRICH 123abc TX
3 lee johnson 225abc TX
4 BERRY CLARK 232ABC TX

Can someone explain where I put a funtion and how to put a function here to
make this all appear mixed in Column A, B, C

Dinee

In D1 enter

=PROPER(A1)

This will make the text in A1 proper case, copy the formula to E1, F1 and G1
to do them all and then copy down the length of the data

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html


"dinee" wrote in message
...
I'm fairly new to functions as need help. I have a workeeet that looks
like
this
A B C D
1 Fname Lname HomeStreet State
2 David HEIDENRICH 123abc TX
3 lee johnson 225abc TX
4 BERRY CLARK 232ABC TX

Can someone explain where I put a funtion and how to put a function here
to
make this all appear mixed in Column A, B, C

Hi dinee,

Not exactly sure what you're after, but I have a clue.

I'm presuming that what you have in the cells is typed directly in (i.e
David in cell A2 is all that's in that cell - no functions are in there).

You can't automatically alter the contents of these cells without losing
what is already in there - i.e there's nothing you can do to make it proper
text (so HEIDENRICH will appear as Heidenrich) and still have it appear in
cell B2.

What you can do, however, is use a function in another cell and then paste
the values from your formatted cell back into cell B2.

With me so far? :p

So try this:
in cell A5 type =LOWER(B4)
this will convert the contents of cell B4 ("BERRY") into lowercase ("berry")

or you could try =UPPER(A3)
which will convert the contents of cell A3 ("lee") into uppercase ("LEE")

and then there's =PROPER(A3)
which will convert the contents of cell A3("lee") into propercase("Lee") -
capitalise the first letter of each word.

Right, now you've got the hang of that put into cell A4 this formula:
=PROPER(A2)
it should show the result as "David"

you know need to drag this formula to cell C7.
With the cell selected there will bo a dark border around the cell, in the
bottom right corner of the border there will be a little square box - when
you hover the curso over this box it will turn to a cross-hair. Click on
this and drag the formula across to cell C5 and then down to row 7.
The formula should copy across and you'll have a copy of your example text
in propercase.

Now highlight all these cells and press Ctrl+C to copy them. Next click
into cell A2 and right-click your mouse to bring up the menu.
Select "Paste Special" and then Values from the dialogue box that appears.

Click OK and the text in your example will be in propercase (but there won't
be any formula in there). You can now delete the formula in the cells A5 to
C7 (A5:C7).

Hope you managed to follow that, and sorry if I went into too much detail as
I've no idea how adept you are at performing other functions within Excel so
I thought I'd better explain it all.





"dinee" wrote:

I'm fairly new to functions as need help. I have a workeeet that looks like
this
A B C D
1 Fname Lname HomeStreet State
2 David HEIDENRICH 123abc TX
3 lee johnson 225abc TX
4 BERRY CLARK 232ABC TX

Can someone explain where I put a funtion and how to put a function here to
make this all appear mixed in Column A, B, C

Darren I was able to follow exatly what you said, and it worked. Now what do
I do to make it work for all the cells in columns A, B, C, and D? My spread
sheet had 675 rows.

"Darren Bartrup" wrote:

Hi dinee,

Not exactly sure what you're after, but I have a clue.

I'm presuming that what you have in the cells is typed directly in (i.e
David in cell A2 is all that's in that cell - no functions are in there).

You can't automatically alter the contents of these cells without losing
what is already in there - i.e there's nothing you can do to make it proper
text (so HEIDENRICH will appear as Heidenrich) and still have it appear in
cell B2.

What you can do, however, is use a function in another cell and then paste
the values from your formatted cell back into cell B2.

With me so far? :p

So try this:
in cell A5 type =LOWER(B4)
this will convert the contents of cell B4 ("BERRY") into lowercase ("berry")

or you could try =UPPER(A3)
which will convert the contents of cell A3 ("lee") into uppercase ("LEE")

and then there's =PROPER(A3)
which will convert the contents of cell A3("lee") into propercase("Lee") -
capitalise the first letter of each word.

Right, now you've got the hang of that put into cell A4 this formula:
=PROPER(A2)
it should show the result as "David"

you know need to drag this formula to cell C7.
With the cell selected there will bo a dark border around the cell, in the
bottom right corner of the border there will be a little square box - when
you hover the curso over this box it will turn to a cross-hair. Click on
this and drag the formula across to cell C5 and then down to row 7.
The formula should copy across and you'll have a copy of your example text
in propercase.

Now highlight all these cells and press Ctrl+C to copy them. Next click
into cell A2 and right-click your mouse to bring up the menu.
Select "Paste Special" and then Values from the dialogue box that appears.

Click OK and the text in your example will be in propercase (but there won't
be any formula in there). You can now delete the formula in the cells A5 to
C7 (A5:C7).

Hope you managed to follow that, and sorry if I went into too much detail as
I've no idea how adept you are at performing other functions within Excel so
I thought I'd better explain it all.





"dinee" wrote:

I'm fairly new to functions as need help. I have a workeeet that looks like
this
A B C D
1 Fname Lname HomeStreet State
2 David HEIDENRICH 123abc TX
3 lee johnson 225abc TX
4 BERRY CLARK 232ABC TX

Can someone explain where I put a funtion and how to put a function here to
make this all appear mixed in Column A, B, C

I'd follow basically what Nick said.
Place the formula in cell E2 (next to your list rather than below it)
=Proper(A2)

then drag across to cell H2.

Next, with cells E2:H2 selected, double-click on that little box and it will
automatically copy the formula down to the bottom of the list (but it will
stop at any empty rows) - or you can manually drag it down to row 675.

Then, with rows E2:H675 selected copy and paste special the values as before.

"dinee" wrote:

Darren I was able to follow exatly what you said, and it worked. Now what do
I do to make it work for all the cells in columns A, B, C, and D? My spread
sheet had 675 rows.

"Darren Bartrup" wrote:

Hi dinee,

Not exactly sure what you're after, but I have a clue.

I'm presuming that what you have in the cells is typed directly in (i.e
David in cell A2 is all that's in that cell - no functions are in there).

You can't automatically alter the contents of these cells without losing
what is already in there - i.e there's nothing you can do to make it proper
text (so HEIDENRICH will appear as Heidenrich) and still have it appear in
cell B2.

What you can do, however, is use a function in another cell and then paste
the values from your formatted cell back into cell B2.

With me so far? :p

So try this:
in cell A5 type =LOWER(B4)
this will convert the contents of cell B4 ("BERRY") into lowercase ("berry")

or you could try =UPPER(A3)
which will convert the contents of cell A3 ("lee") into uppercase ("LEE")

and then there's =PROPER(A3)
which will convert the contents of cell A3("lee") into propercase("Lee") -
capitalise the first letter of each word.

Right, now you've got the hang of that put into cell A4 this formula:
=PROPER(A2)
it should show the result as "David"

you know need to drag this formula to cell C7.
With the cell selected there will bo a dark border around the cell, in the
bottom right corner of the border there will be a little square box - when
you hover the curso over this box it will turn to a cross-hair. Click on
this and drag the formula across to cell C5 and then down to row 7.
The formula should copy across and you'll have a copy of your example text
in propercase.

Now highlight all these cells and press Ctrl+C to copy them. Next click
into cell A2 and right-click your mouse to bring up the menu.
Select "Paste Special" and then Values from the dialogue box that appears.

Click OK and the text in your example will be in propercase (but there won't
be any formula in there). You can now delete the formula in the cells A5 to
C7 (A5:C7).

Hope you managed to follow that, and sorry if I went into too much detail as
I've no idea how adept you are at performing other functions within Excel so
I thought I'd better explain it all.





"dinee" wrote:

I'm fairly new to functions as need help. I have a workeeet that looks like
this
A B C D
1 Fname Lname HomeStreet State
2 David HEIDENRICH 123abc TX
3 lee johnson 225abc TX
4 BERRY CLARK 232ABC TX

Can someone explain where I put a funtion and how to put a function here to
make this all appear mixed in Column A, B, C