Can I use Concatenate function for diff amunts of cells that need to be pulled together?

[NERINGA]

Hi all,

I am trying to pull information together, by using the concatinate function, but I'm not even sure if it is possible to do so.

Let's say I have:

A1 - text B1 - number
A2 - Blank B2 - number
A3 - text B3 - number
A4 - Blank B4 - number
A5 - Blank B5 - number

What i need to do is pull the numbers in B1 & B2 together into B1, and numbers in B3, B4 & B5 into B3. The question is, can i actually do that? Since there is a different amount of blank cells between the cells that contain text. I know you're thinking that I could do it manually, but trust me this goes on up to A3000 or something.

Aaand on top of that, not all the rows that have blanks in column A have numbers in column B... soo yeah.

If anyone knows how to do this, please please please tell me!! That would be such a big help.
And even if it is impossible to do that, does anyone have any suggestions of how to make it simpler? Cause I think it would take a looong time to go through over 3000 rows and pull them together.

Thanks everyone

[NERINGA]

Since no one replied, I assume it's impossible. Oh the joy of excell....

[Rambo4711]

You cannot create a formula for a cell containing a value to replace the existing value.

Then you must use a helper column.

Can you post a small sample of your spreadsheet?

[NERINGA]

Thank you so much for replying!!!

That's what i thought too about replacing values.. and there is no problem with using a helper column, but the difficult part is to make them concatenate, I am really struggling.

My spreadsheet goes something like this: (I changed the information, this is just an idea of how it is supposed to be, I just pasted it straight on here if you don't mind)


Code Name Number
100000 AAA
5678
9102

100001 BBB 4567

2345
100002 CCC
1234
8910

2345
5678

Thank You

[NERINGA]

Crap, it posted it all funny, sorry!!!

[Rambo4711]

This is a solution with a helper column.

The data is concatenated with a user defined formula named "concat". This is defined in the VBA monitor. Type alt+F11 to see the code.

[NERINGA]

Thank you very much for your help, but I think you missunderstood me. You concatinated the columns "code", "name" and "number".

But I wanted to only concatinate the numbers. For example if you refer to my sample, I would want the information in cells C2, C3, C4 and C5 to be together in one cell. And I would want to do this for every code.

I have actually progressed, I used this formula:

=IF(A2=0," ",SUBSTITUTE(TRIM(C2&" "&C3&" "&C4&" "&C5)," "," , "))

It works beatifully, as it ignores the blanks and only uses the formula where there is a code present. BUT my biggest problem is still not solved. In my formula I used four of the cells: (C2&" "&C3&" "&C4&" "&C5) but as you know in other cases there are more or less than four cells that contain numbers which need to be pulled together!

Aaargh this is so frustrating! I will try to play around with the IF function and see if anything happens...
Thank you very very much for this though!! I appreciate your help

If anyone has any suggestions, please help, and tell me if I'm not being clear enough Thank You.

[FDibbins]

Hi

Im not going to do the whol thing, but try this...

=IF(A2=0," ",SUBSTITUTE(TRIM(IF(ISTEXT(C2),"",C2)&" "&C3&" "&C4&" "&C5)," "," , "))

[NERINGA]

Hi,

Thank You for replying!!

I tried it, but it still didn't solve the problem. :/

It works in the same way my formula does, if there are more than four numbers, then the formula still doesn't include them.If there are 3 numbers then the formula uses a number from the next code.

[FDibbins]

It's not including more than 4, because thats all there is in the formula.

can you upload a sample workbook with a few samples of dummy info in it?

[NERINGA]

Hi,

I know that there are only four cells in the formula, but I'm not sure if you can change the number automatically.
Because I would have to change the number of the cells for every code there is, and there is a lot.

I think that what I am trying to achieve is impossible, because excel would have to recognise that a different code has started, and change the formula.

And I did upload the sample spreadsheet..

[NERINGA]

Is there any way of typing into the formula; use cells such and such, up until there is a number in column A; or something like that??

[NERINGA]

I almost figured it out, I used this formula:

=IF(A4=0,IF(A3=0,IF(A2=0," ",SUBSTITUTE(TRIM(C2&" "&C3&" "&C4&" "&C5)," "," , ")),SUBSTITUTE(TRIM(C2)," "," , ")),SUBSTITUTE(TRIM(C2&" "&C3)," "," , "))

And I wanted to add: =IF(A5=0...... etc. But the formula is too long and it wont let me..