Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

=UPPER(LEFT(A1,3)&" "&RIGHT(A1,3))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"LB" wrote in message
...
Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

Thanks for the info. I'm not sure how to format the entire column so that
when I type b2j3e6 in one row, then m4y1k9 in another, they change.

lb
"ufo_pilot" wrote in message
...
=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

In an adjacent column............say H

=UPPER(LEFT(G1,3)&" "&RIGHT(G1,3))

Double-click on the fill handle of H1 to fill down.


Gord Dibben MS Excel MVP


On Thu, 25 Jan 2007 12:22:30 -0400, "LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

Take the formula you were given & copy it as far as you like down the
column. Note that this won't change what is in your original column, but
will give the output in a separate column where you've put your formula.
After that, if you wish to you can replace the original column by doing a
copy & paste special/ values.
--
David Biddulph

"LB" wrote in message
...
Thanks for the info. I'm not sure how to format the entire column so that
when I type b2j3e6 in one row, then m4y1k9 in another, they change.

"ufo_pilot" wrote in message
...
=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

For new entries you could use event code to change them as you entered them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 7 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Select the sheet tab and "View Code".

Copy/paste the above into that module.

As written operates only on column G........Target.Column < 7

Existing entries in Column G can be changed by selecting each cell and F2 then
ENTER


Gord Dibben MS Excel MVP

On Thu, 25 Jan 2007 13:23:51 -0400, "LB" wrote:

Thanks for the info. I'm not sure how to format the entire column so that
when I type b2j3e6 in one row, then m4y1k9 in another, they change.

lb
"ufo_pilot" wrote in message
...
=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.