Concatenate function

[JOONA]

Hi Have used the following function to combine 3 cells into one, this works fine except i have the following issues.

1. there is no space between each word,
2. if one cell has no data to combine it displays a 0

How can i get rid of the 0's in this forumlae

=CONCATENATE(D6,E6,F6)
A STEWART - 50005164A DUNCAN - 50005164A EGLINGTON - 50005163


Need it to show
A STEWART - 50005164 A DUNCAN - 50005164 A EGLINGTON - 50005163

[AlKey]

Try this

=(D6&" "&E6&" "&F6)

[JOONA]

Thanks but how do i get rid of the 0's

[JOONA]

I have tried this but it does not display spaces =IF(LEFT(D10,1)="0",REPLACE(D10,1,3," "),D10)&IF(LEFT(E10,1)="0",REPLACE(E10,1,3," "),E10)&IF(LEFT(F10,1)="0",REPLACE(F10,1,3," "),F10)

This Displays A STEWART - 50005164A DUNCAN - 50005164A EGLINGTON - 5000516 when it should display A STEWART - 50005164 A DUNCAN - 50005164 A EGLINGTON - 50005163

[AlKey]

I don't have your spreadsheet and I have no idea that you 0. upload a sample workbook with data and desired output

To upload a file click on Go Advanced at the bottom of your screen then click on PaperClip icon and Add Files, Select File, locate your file and click Open then click Upload and click Done

[JOONA]

Example of what the forumale does A Jones - 50005164, 0 - L Jones 50005165, P Jones - 50005163

I dont want the xero to appear. plus i need each name and number to be spaced out as above

The 3 cells that i am combining into one contan text and numbers

[JOONA]

Basicaly I want to ignore the null values and for each cell i am combining into one to have it spaced out like a jones, p jones, c jones

[Tony Valko]

Try this...

=TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(
D6&","&E6&","&F6," ","~"),","," "))," ",", "),"~"," "))

[clabulis]

Try this:

=T(D6&" ")&T(E6&" ")&T(F6&" ")

[Tony Valko]

Try this:

=T(D6&" ")&T(E6&" ")&T(F6&" ")

That can have leading/trailing space characters.

Add TRIM to get rid of them:

=TRIM(T(D6&" ")&T(E6&" ")&T(F6&" "))

The formula I suggested adds a comma delimiter between cell entries. After reading the thread again it doesn't look like the OP wants that.

[JOONA]

Nope trim not working

[boopathiraja]

Try
=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)

[JOONA]

Thanks it works it remvoes the xero's but 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

[sktneer]

Did you pay attention to what Alkey suggested in post#5 ???

[humdingaling]

is xero always at the end or will you have xero in middle sometimes?

[humdingaling]

actually..doesnt matter
see attached

[boopathiraja]

Try
=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&" ")

[JOONA]

Yes but i am at work and can not upload stuff

[humdingaling]

does that mean you cant download the file i uploaded?
assume a/b/c is your data

Please Login or Register  to view this content.

[sktneer]

does that mean you cant download the file i uploaded?

I think OP was responding to post#14.

[humdingaling]

shrugs..assumed if you cant upload you cant download as well so i posted my formula instead

[Tony Valko]

Try this tweaked version of the formula from post #8. It does not add the comma delimiter.

=TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(
D6&","&E6&","&F6," ","~"),","," "))," "," "),"~"," "))