Results 1 to 2 of 2

Thread: Concatenate Help

  1. #1

    Concatenate Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •