Concatenate Help

JOONA

New member
Joined
Dec 9, 2013
Messages
27
Reaction score
0
Points
0
I have 3 cells that i want to combine into one each cell contains txt and numbers. i have used the conatenate forumale but if one of the 3 cells contain no information it displays a zero i Need it so it does not display 0 ie the null value.

I have also tried the following =IF(LEFT(D10,1)="0",REPLACE(D10,1,1," "),D10)&IF(LEFT(E10,1)="0",REPLACE(E10,1,1," "),E10)&IF(LEFT(F10,1)="0",REPLACE(F10,1,1," "),F10) forumla this works but it does not put spaces in it displays the following.
A STEWART - 50005164A DUNCAN - 50005165A EGLINGTON - 50005163
A STEWART - 50005164A DUNCAN - 50005165A EGLINGTON - 50005163
A STEWART - 50005164A DUNCAN - 50005165A EGLINGTON - 50005163
A STEWART - 50005164A EGLINGTON - 50005163

When I want it to display it like this

A STEWART - 50005164 A DUNCAN - 50005165 A EGLINGTON - 50005163
A STEWART - 50005164 A DUNCAN - 50005165 A EGLINGTON - 50005163
A STEWART - 50005164 A DUNCAN - 50005165 A EGLINGTON - 50005163
A STEWART - 50005164 A EGLINGTON - 50005163
Can anyone help with this function
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I can't see how a blank would pass the 0 test, but you say it does so try this

=TRIM(IF(LEFT(D10,1)="0","",D10)&IF(LEFT(E10,1)="0",""," "&E10)&IF(LEFT(F10,1)="0",""," "&F10))
 
Top