Help making a period appear only if there is a character in the cell.

[TWTHOMAS]

I am using this formula to get a middle initial from a name in column A

Names in Column A are like:

Thomas Dan J
Jones Susan
Ayers John B II

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&"."

I want to only have the period (".") appear if there is a middle initial.

Currently Susan Jones has a lone . in the middle initial field because she doesn't have a middle initial. Is there a way to make that period conditional based on if there is a character available?

 

[mikerickson]

How about
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1&" "," ",REPT(" ",255),2),255)),1) & REPT(".", 1<(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

 

[steve the fish]

=If(formula=".","",formula)

So in your case this should work:

=IF(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&"."=".","",TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")

 

[Special-K99]

Am sure there's a better way but try

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&if(LEN(A1)-LEN(SUBSTITUTE(A1," ","")) > 1,".","")

 

[TWTHOMAS]

How about
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1&" "," ",REPT(" ",255),2),255)),1) & REPT(".", 1<(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

This removes the period but also abbreviates full middle names to just an initial.

Can it be adjusted to not do that?

 

[TWTHOMAS]

=If(formula=".","",formula)

So in your case this should work:

=IF(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&"."=".","",TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")

This one works perfectly but happens to reveal a new issue. When an employee has a full middle name prefered and we can't make it just an initial. Is there another adjustment to make it so if the middle name / initial is more that one character, it also doesn't have the period?

So the name

Jones David Joseph

ends up with Joseph in the column instead of Joseph. (with a period).

Or Thompson Jacob II is split so II ends up in the cell without a period.

 

[steve the fish]

Its a bit long and maybe theres better way but seems to do what you require:


=IF(IF(LEN(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")>2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1))),TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")=".","",IF(LEN(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")>2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1))),TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&"."))

 

[TWTHOMAS]

Perfect!!
Thanks

Its a bit long and maybe theres better way but seems to do what you require:


=IF(IF(LEN(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")>2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1))),TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")=".","",IF(LEN(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&".")>2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1))),TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:C1)-1)*LEN($A1))+1,LEN($A1)))&"."))

 

[Peter_SSs]

Do you need to have the same (very long) formula for all columns?

Might something like this be acceptable?
For the moment I've assumed no more than 4 answer columns required.

Some more varied sample data & expected results might help if this isn't sufficient.

Excel Workbook
	A	B	C	D	E
2	Thomas Dan J	Thomas	Dan	J.
3	Jones Susan	Jones	Susan
4	Ayers John B II	Ayers	John	B.	II
5	Jones David Joseph	Jones	David	Joseph
Split Names

 

[TWTHOMAS]

These appear to work okay also. The only issues I see are two.

1. This still leaves a period in Column D when there is no middle initial or suffix. (i.e. Doe John)

2. This also shows #VALUE! where the formula appears and there is no name data but only in columns B and D.

Much smaller formulas though which is always easier to deal with.

Thanks!

Do you need to have the same (very long) formula for all columns?

Might something like this be acceptable?
For the moment I've assumed no more than 4 answer columns required.

Some more varied sample data & expected results might help if this isn't sufficient.

Split Names

	A	B	C	D	E
2	Thomas Dan J	Thomas	Dan	J.
3	Jones Susan	Jones	Susan
4	Ayers John B II	Ayers	John	B.	II
5	Jones David Joseph	Jones	David	Joseph

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 133px"><COL style="WIDTH: 77px"><COL style="WIDTH: 69px"><COL style="WIDTH: 75px"><COL style="WIDTH: 45px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula B2 =LEFT(A2,FIND(" ",A2)-1) C2 =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100)) D2 =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),200,100))&IF(MID(A2&" ",LEN(B2&C2)+4,1)=" ",".","") E2 =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),300,100)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4