Excel Problem

[Joe_Fox]

Hello,

I have some cells I want to merge into one cell, for example:

figure 1.

  A   B   C   D 
1 23  66  24  87
2 34  56      67

I want column E to be filled with the merged data but with each cell being separted by a semi-colon, so it should look like this:

figure 2.

  A   B   C   D   E
1 23  66  24  87  23;66;24;87
2 34  56      67  34;56;67

I am using the formula E1=A1&";"&A2&";"&A3&";"&A4.

My problem is that when a cell is blank, like C2, the result is that a semi-colon is placed in the merged cell, resulting in this:

figure 3.

  A   B   C   D   E
1 23  66  24  87  23;66;24;87
2 34  56      67  34;56;;67

How can I make the merged cell look like it does in figure 2?

I think I need to use some IF functions but how do I say ignore blanks?

Any help would be hugely appreciated.
Thanks,
Joe_Fox

 

[Han]

people must've got fed up with helping you

 

[OldGit]

Highlight the exta ; then press the delete key.

 

[Macky]

Highlight the exta ; then press the delete key.

:icon_lol:

 

[Joe_Fox]

i sorted it myself, it required some brain work but i got there in the end. thanks anyway.

 

[Jeff]

Highlight the exta ; then press the delete key.

But that wouldn't work

 

[OldGit]

But that wouldn't work

Oh dear :icon_lol:

 

[bocadillo]

i sorted it myself, it required some brain work but i got there in the end. thanks anyway.

Would you like to share the solution, Joe.

 

[Matt_B]

surely just wrap everything in if statements?

E1=A1&if(A1 <> "" , ";", "")&A2&if(A2 <> "" , ";", "")&A3&if(A3 <> "" , ";", "")&A4.

 

[Durham Fox]

Ever considered a VBA course Joe?...

 

[Joe_Fox]

Ever considered a VBA course Joe?...

Yes, have you?

 

[Durham Fox]

Yes, have you?

No.

 

[Darth Vodka]

oh an excel one

soz, have been changing jobs lately...

PM me when you post an excel one, there's not much i don't know about excel, access & VBA

 

[Steven]

oh an excel one

soz, have been changing jobs lately...

PM me when you post an excel one, there's not much i don't know about excel, access & VBA

What are you doing now then? Liquid Analyst didn't do it for you? :icon_conf :icon_conf

 

[Macky]

..PM me when you post an excel one, there's not much i don't know about excel, access & VBA

I don't know what it is but, I hate all having to use Excel & Access, well, databases & spreadsheets fullstop. They make my head hurt. :icon_lol:

I always delegate, where ever possible, at work.

 

[Darth Vodka]

What are you doing now then? Liquid Analyst didn't do it for you? :icon_conf :icon_conf

i'm a Tactical Analyst now, spreadsheets and databases, similar job, different bank

 

[Joe_Fox]

hbos?

 

[Darth Vodka]

hbos?

nope

 

[springy]

Ask Leszek to send you the answer 'via the wire'. You uploaded that video yet?