Just add spaces in between 3 words

[hsandeep]

How to concatenate FIRST SECOND & THIRD name in a single cell separated by a blank.
A1: Mark
B1: Hu.
C1=England

Answer:
D1: Mark Hu. England

 

[hsandeep]

Wow this works.
Can i get:
Example 1;
A1:Mark
B1: Human
C1: England
D1: M.H.England (for FIRST & SECOND word non blank)
Example 2;
A1: Mark
B1: Human
C1: ""
D1: M.Human
Example 3;
A1: Mark
B1: ""
C1: ""
D1: Mark
Example 4;
A1: Mark
B1: ""
C1: England
D1: M.England
i.e. LAST word must appear FULL & previous words in initial with "."

 

[Matty]

Sounds like:

=IF(COUNTIF(A1:C1,"?*")=3,LEFT(A1,1)&"."&LEFT(B1,1)&"."&C1,IF(B1<>"",LEFT(A1,1)&"."&B1,IF(C1<>"",LEFT(A1,1)&"."&C1,A1)))

Matty

 

[hsandeep]

Works....a small mistake...
For A1, B1 & C1 = "" (blank) NOT through formula the result is coming 0, Can it come "" (blank).
For A1, B1 & C1 = "" (blank) THROUGH formula the result is coming "" (blank). It is OK.

 

[Rick Rothstein]

Wow this works.
Can i get:
Example 1;
A1:Mark
B1: Human
C1: England
D1: M.H.England (for FIRST & SECOND word non blank)
Example 2;
A1: Mark
B1: Human
C1: ""
D1: M.Human
Example 3;
A1: Mark
B1: ""
C1: ""
D1: Mark
Example 4;
A1: Mark
B1: ""
C1: England
D1: M.England
i.e. LAST word must appear FULL & previous words in initial with "."

I am pretty sure this formula will work correctly...

=SUBSTITUTE(TRIM(IF(COUNTA(A1:A3),TRIM(LEFT(TRIM(LEFT(A1)&" "&LEFT(A2)&" "&LEFT(A3)),LEN(TRIM(LEFT(A1)&" "&LEFT(A2)&" "&LEFT(A3)))-1))&" "&LOOKUP("zzzz",A1:A3),""))," ",".")

 

[hsandeep]

For A1:Mark
B1: Human
C1: "" (blank) thr' formula
D1:M (This is WRONG). I need
D1: M.Human (CORRECT)

 

[Rick Rothstein]

For A1:MarkB1: HumanC1: "" (blank) thr' formulaD1:M (This is WRONG). I needD1: M.Human (CORRECT)

Sorry, I developed my formula for cells A1:A3, not A1:C1. Here is the corrected formula...=SUBSTITUTE(TRIM(IF(COUNTA(A1:C1),TRIM(LEFT(TRIM(LEFT(A1)&" "&LEFT(B1)&" "&LEFT(C1)),LEN(TRIM(LEFT(A1)&" "&LEFT(B1)&" "&LEFT(C1)))-1))&" "&LOOKUP("zzz",A1:C1),""))," ",".")

 

[hsandeep]

Here is the corrected formula...=SUBSTITUTE(TRIM(IF(COUNTA(A1:C1),TRIM(LEFT(TRIM(LEFT(A1)&" "&LEFT(B1)&" "&LEFT(C1)),LEN(TRIM(LEFT(A1)&" "&LEFT(B1)&" "&LEFT(C1)))-1))&" "&LOOKUP("zzz",A1:C1),""))," ",".")[/QUOTE]
No Same WRONG answer is coming. i.e. D1:M (WRONG). I need D1: M.Human (CORRECT)

 

[Rick Rothstein]

No Same WRONG answer is coming. i.e. D1:M (WRONG). I need D1: M.Human (CORRECT)

I modified the formula slightly; see if this one works for you....

=SUBSTITUTE(TRIM(IF(A1&B1&C1="","",TRIM(LEFT(TRIM(LEFT(A1)&" "&LEFT(B1)&" "&LEFT(C1)),LEN(TRIM(LEFT(A1)&" "&LEFT(B1)&" "&LEFT(C1)))-1))&" "&LOOKUP(2,1/(A1:C1<>""),A1:C1)))," ",".")

 

[hsandeep]

It DOES, Rick