Is it possible

[Help on formula]

Hi,
I have two columns of names A and B (A=First name B=Last name). My question
is, without changing the column A and B, how could be the Macro defined so
that column A’s or B’s characters are if less than four, the comas (,)
inserts into to make it four i.e. “Leo=LEO,†“A=A,,,â€. Blank fields should
stay blank and if it’s possible if the small letters of name in column A or B
can also be capitalized in the same Macro. To explore the possibilities I
thanks in advance…

Sincerely Shan

 

[Jim Thomlinson]

Something like this should do...
=IF(LEN(A1) = 0, "", IF(LEN(A1)>= 4, UPPER(A1), UPPER(A1) & REPT(",",
4-LEN(A1))))

 

[p45cal]

Hi,
I have two columns of names A and B (A=First name B=Last name). M
question
is, without changing the column A and B, how could be the Macro define
so
that column A’s or B’s characters are if less than four, the coma
(,)
inserts into to make it four i.e. “Leo=LEO,†“A=A,,,â€. Blan
fields should
stay blank and if it’s possible if the small letters of name i
column A or B
can also be capitalized in the same Macro. To explore the possibilitie
I
thanks in advance…

Sincerely Shan

In C1:
=UPPER(IF(LEN(A4)<4,LEFT(A4 & ",,,,",4),A4))
and copy to range C11 and down as far as necessary

 

[Help on formula]

Thank you very much for your response. I am trying to avoid creating
additional column/s because the data will be inserted in fields by copy past
process (source to destination). For example the source fields are A B C D
and E (First Name, Last Name, Address, City and State) which will be pasted
into destination fields of A B C D and E columns, if the destination fields
are different than the source it won't work. Please help to resolve it by
Marco to be defined in one of the row in destination file. Thank you very much
Sincerely Shan

 

[p45cal]

Thank you very much for your response. I am trying to avoid creating
additional column/s because the data will be inserted in fields by cop
past
process (source to destination). For example the source fields are A
C D
and E (First Name, Last Name, Address, City and State) which will b
pasted
into destination fields of A B C D and E columns, if the destinatio
fields
are different than the source it won't work. Please help to resolve i
by
Marco to be defined in one of the row in destination file. Thank yo
very much
Sincerely Shan

The following macro works on columns A and B of the used range portio
of the active sheet:
Sub blah()
For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B"))
If Len(cll.Value) < 4 And cll.Value <> "" Then cll.Value
Left(cll.Value & ",,,,", 4)
cll.Value = UCase(cll.Value)
Next cll
End Su

 

[Help on formula]

I think it’s almost done, the only thing is a syntax error which I unable to
find built-in help to correct it, I know it's probably me. Please help me to
correct it I will be much appreciated.

If Len(cll.Value) < 4 And cll.Value <> "" Then cll.Value =
Left(cll.Value & ",,,,", 4)

 

[p45cal]

I think it’s almost done, the only thing is a syntax error which
unable to
find built-in help to correct it, I know it's probably me. Please hel
me to
correct it I will be much appreciated.

If Len(cll.Value) < 4 And cll.Value <> "" Then cll.Value =
Left(cll.Value & ",,,,", 4)

The above is on one line.
Copy/paste the code from thecodecage.com site; the link should be i
the foote

 

[Help on formula]

Thank you very much from the bottom my hart! It works. I wish if the names
could also be the capitalized. Please..

 

[Help on formula]

Oh, never mind it does all. I can't believe it!
I thank very much, what a Good Man you are!

Thank you very much from the bottom my hart! It works. I wish if the names
could also be the capitalized. Please..