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
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