Combine cell info into one cell

[Maggie]

I have a worksheet where one cell is basically where information is
dumped into it from another cell. I want to have the cell that has an
issue be numbered 1,2,3 and be dumped cell with the numbers. Is there
a way to do that?

For example:
Cell A1 has Missing file
Cell A6 has No Coverage
Cell A20 reads 1. Missing file. 6. No Coverage.

 

[jknkboaters]

Give this a try:

In cell A20 entered this formula
=ROW(A1)&". "&A1&". "&ROW(A6)&". "&A6&"."

Hope that helps,
Jim K

 

[ker_01]

Maggie- this could be done (with brute force) using straight formulas, but
I'm assuming that you are looking for a VBA solution.

Will all records have at least one "problem" to be on your list, or will
there be rows where there are no problems/information, and you want A20 to
remain blank?

 

[Maggie]

Maggie- this could be done (with brute force) using straight formulas, but
I'm assuming that you are looking for a VBA solution.

Will all records have at least one "problem" to be on your list, or will
there be rows where there are no problems/information, and you want A20 to
remain blank?






- Show quoted text -

There will be blanks in some of the fields. I already have this
formula but I dont like that when something is not entered into the
cell the number still remains in the B232 cell. I only want the
numbers present when data is entered into the cell. Does that make
sense?
Here is my formula:
="1. "&""&E21&" "&F21&" 2. "&E29&" "&F29&" 3. "&E33&"
"&F33&" 4. "&E37&" "&F37&" 5. "&E44&" "&F44&" 6a. "&E51&"
"&F51&" 6b. "&E55&" "&F55&" 6c. "&E59&""&F59&" 6d.
"&E63&" "&F63&" 7. "&E67&""&F67&" 8. "&E71&" "&F71&" 9.
"&E75&" "&F75&" 10a. "&E82&" "&F82&" 11. "&E87&" "&F87&"
12. "&E91&" "&F91&" 13. "&E97&" "&F97&" 14. "&E107&"
"&F107&" 15. "&E111&" "&F111&" 16. "&E115&""&F115&" 17.
"&E119&" "&F119&" 18a. "&E126&" "&F126&" 19. "&E130&"
"&F130&" 20. "&E142&" "&F142&" 21a. "&E149&" "&F149&" 22.
"&E155&" "&F155&" 23. "&E165&" "&F165&" 24a. "&E178&"
"&F178&" 25a. "&E185&" "&F185&" 25c. "&E194&" "&F194&"
26a. "&E203&" "&F203&" 26b. "& E207 &" "&F207 &" 27. "& E211&"
"&F211&" 28a. "& E215&" "& F215&" 29. "&E222 &" "& F222&"
29a. "&E226&" "& F226

Thanks!

 

[ker_01]

Well, based on your formula it looks like you are going with a brute force
solution. Here are a few thoughts;

(1) To avoid having the extra numbers show up, you will have to determine
whether each cell is empty. Your base formula for each cell, instead of
simply "1. "& E21... you will have an if statement, something like:

if(E21<>"","1. "& E21 & ", ","")

This checks to see if the cell is blank, if not it puts together the output
for just that one cell, otherwise, it returns a zero-length string. Note that
it adds a comma (and space) on the back of whatever it returns, to make it
easier to string multiple cells together.

Now you (may) have two problems when you string multiple statements
together; first, I don't use XL2007, but in 2003 and earlier I believe you
may run into limits on the number of conditions that can be in a formula, or
the overall length of the formula in one cell. Not to worry- if needed, you
can build pieces of the combined formula (below) in multiple hidden columns,
and then use your overall output cell to pull together those pieces. The
other problem is that you will have an extra comma on the end of your output,
but I'll discuss that below.

So now you may have a cell [AA12] with something like:

[AA12]=Concatenate(if(E21<>"","1. "&E21,""),if(F21<>"","2.
"&F21,""),if(G21<>"","3. "&G21,""),if(H21<>"","4. "&H21,""),if(I21<>"","5.
"&I21,""))

and another cell [AB12] with

[AB12]=Concatenate(if(J21<>"","6. "&J21,""),if(K21<>"","7.
"&K21,""),if(L21<>"","8. "&L21,""),if(M21<>"","9. "&M21,""),if(N21<>"","10.
"&N21,""))

Let's say that the results in the first cell ends up being:
1. Missing file,

And the second cell ends up as:
6. No Coverage,

So your final cell could just be
=concatenate (AA21, AB21)
and would show

1. Missing file, 6. No Coverage,

The problem is that you have a trailing comma, which may be distracting. If
you knew you had at least one item on every line, it would be easy to use a
=Left statement to clean it up by removing just the last two characters;
= Left(concatenate (AA21, AB21),len(concatenate (AA21, AB21)-2)
this would pull off the comma and the final space. However, if you have rows
that won't have any comments at all, you'll need another IF statement to make
sure that the final value has at least 2 characters before you try to remove
the last two (otherwise you will get an error)

=IF(Len(concatenate (AA21, AB21))>2, Left(concatenate (AA21,
AB21),len(concatenate (AA21, AB21)-2),"")

This is all aircode (well, air-formula, anyway), but it should put you on
the right path.

HTH,
Keith

 

[Maggie]

Well, based on your formula it looks like you are going with a brute force
solution. Here are a few thoughts;

(1) To avoid having the extra numbers show up, you will have to determine
whether each cell is empty. Your base formula for each cell, instead of
simply "1. "& E21... you will have an if statement, something like:

if(E21<>"","1. "& E21 & ", ","")

This checks to see if the cell is blank, if not it puts together the output
for just that one cell, otherwise, it returns a zero-length string. Note that
it adds a comma (and space) on the back of whatever it returns, to make it
easier to string multiple cells together.

Now you (may) have two problems when you string multiple statements
together; first, I don't use XL2007, but in 2003 and earlier I believe you
may run into limits on the number of conditions that can be in a formula,or
the overall length of the formula in one cell. Not to worry- if needed, you
can build pieces of the combined formula (below) in multiple hidden columns,
and then use your overall output cell to pull together those pieces. The
other problem is that you will have an extra comma on the end of your output,
but I'll discuss that below.

So now you may have a cell [AA12] with something like:

[AA12]=Concatenate(if(E21<>"","1. "&E21,""),if(F21<>"","2.
"&F21,""),if(G21<>"","3. "&G21,""),if(H21<>"","4. "&H21,""),if(I21<>"","5..
"&I21,""))

and another cell [AB12] with

[AB12]=Concatenate(if(J21<>"","6. "&J21,""),if(K21<>"","7.
"&K21,""),if(L21<>"","8. "&L21,""),if(M21<>"","9. "&M21,""),if(N21<>"","10.
"&N21,""))

Let's say that the results in the first cell ends up being:
1. Missing file,

And the second cell ends up as:
6. No Coverage,

So your final cell could just be
=concatenate (AA21, AB21)
and would show

1. Missing file, 6. No Coverage,

The problem is that you have a trailing comma, which may be distracting. If
you knew you had at least one item on every line, it would be easy to usea
=Left statement to clean it up by removing just the last two characters;
= Left(concatenate (AA21, AB21),len(concatenate (AA21, AB21)-2)
this would pull off the comma and the final space. However, if you have rows
that won't have any comments at all, you'll need another IF statement to make
sure that the final value has at least 2 characters before you try to remove
the last two (otherwise you will get an error)

=IF(Len(concatenate (AA21, AB21))>2, Left(concatenate (AA21,
AB21),len(concatenate (AA21, AB21)-2),"")

This is all aircode (well, air-formula, anyway), but it should put you on
the right path.

HTH,
Keith

There will be blanks in some of the fields.  I already have this
formula but I dont like that when something is not entered into the
cell the number still remains in the B232 cell.  I only want the
numbers present when data is entered into the cell.  Does that make
sense?
Here is my formula:
="1. "&""&E21&" "&F21&"     2. "&E29&" "&F29&"     3. "&E33&"
"&F33&"     4. "&E37&" "&F37&"     5. "&E44&" "&F44&"     6a. "&E51&"
"&F51&"     6b. "&E55&" "&F55&"     6c. "&E59&""&F59&"      6d.
"&E63&" "&F63&"      7. "&E67&""&F67&"     8. "&E71&" "&F71&"    9.
"&E75&" "&F75&"     10a. "&E82&" "&F82&"     11. "&E87&"  "&F87&"
12. "&E91&" "&F91&"     13. "&E97&" "&F97&"     14. "&E107&"
"&F107&"     15. "&E111&" "&F111&"     16. "&E115&""&F115&"    17.
"&E119&" "&F119&"     18a. "&E126&" "&F126&"     19. "&E130&"
"&F130&"     20. "&E142&" "&F142&"     21a. "&E149&" "&F149&"     22.
"&E155&" "&F155&"     23. "&E165&" "&F165&"     24a. "&E178&"
"&F178&"     25a. "&E185&" "&F185&"     25c. "&E194&" "&F194&"
26a. "&E203&" "&F203&"     26b. "& E207 &" "&F207 &"     27. "&E211&"
"&F211&"     28a. "& E215&" "& F215&"     29. "&E222 &" "& F222&"
29a. "&E226&" "& F226

Thanks!- Hide quoted text -

- Show quoted text -

Thanks but I also want the info that is put into E21 and F21 to be
labeled 1 and E29 and F29 to be 2 and so on. How would you write the
code for that because here it is putting E21 as 1 and F21 as 2?

Maggie

 

[ker_01]

Well, based on your formula it looks like you are going with a brute force
solution. Here are a few thoughts;

(1) To avoid having the extra numbers show up, you will have to determine
whether each cell is empty. Your base formula for each cell, instead of
simply "1. "& E21... you will have an if statement, something like:

if(E21<>"","1. "& E21 & ", ","")

This checks to see if the cell is blank, if not it puts together the output
for just that one cell, otherwise, it returns a zero-length string. Note that
it adds a comma (and space) on the back of whatever it returns, to make it
easier to string multiple cells together.

Now you (may) have two problems when you string multiple statements
together; first, I don't use XL2007, but in 2003 and earlier I believe you
may run into limits on the number of conditions that can be in a formula, or
the overall length of the formula in one cell. Not to worry- if needed, you
can build pieces of the combined formula (below) in multiple hidden columns,
and then use your overall output cell to pull together those pieces. The
other problem is that you will have an extra comma on the end of your output,
but I'll discuss that below.

So now you may have a cell [AA12] with something like:

[AA12]=Concatenate(if(E21<>"","1. "&E21,""),if(F21<>"","2.
"&F21,""),if(G21<>"","3. "&G21,""),if(H21<>"","4. "&H21,""),if(I21<>"","5..
"&I21,""))

and another cell [AB12] with

[AB12]=Concatenate(if(J21<>"","6. "&J21,""),if(K21<>"","7.
"&K21,""),if(L21<>"","8. "&L21,""),if(M21<>"","9. "&M21,""),if(N21<>"","10.
"&N21,""))

Let's say that the results in the first cell ends up being:
1. Missing file,

And the second cell ends up as:
6. No Coverage,

So your final cell could just be
=concatenate (AA21, AB21)
and would show

1. Missing file, 6. No Coverage,

The problem is that you have a trailing comma, which may be distracting. If
you knew you had at least one item on every line, it would be easy to use a
=Left statement to clean it up by removing just the last two characters;
= Left(concatenate (AA21, AB21),len(concatenate (AA21, AB21)-2)
this would pull off the comma and the final space. However, if you have rows
that won't have any comments at all, you'll need another IF statement to make
sure that the final value has at least 2 characters before you try to remove
the last two (otherwise you will get an error)

=IF(Len(concatenate (AA21, AB21))>2, Left(concatenate (AA21,
AB21),len(concatenate (AA21, AB21)-2),"")

This is all aircode (well, air-formula, anyway), but it should put you on
the right path.

HTH,
Keith

Maggie- this could be done (with brute force) using straight formulas, but
I'm assuming that you are looking for a VBA solution.

Will all records have at least one "problem" to be on your list, or will
there be rows where there are no problems/information, and you want A20 to
remain blank?

:
I have a worksheet where one cell is basically where information is
dumped into it from another cell. I want to have the cell that has an
issue be numbered 1,2,3 and be dumped cell with the numbers. Is there
a way to do that?

For example:
Cell A1 has Missing file
Cell A6 has No Coverage
Cell A20 reads 1. Missing file. 6. No Coverage.- Hide quoted text -

- Show quoted text -

There will be blanks in some of the fields. I already have this
formula but I dont like that when something is not entered into the
cell the number still remains in the B232 cell. I only want the
numbers present when data is entered into the cell. Does that make
sense?
Here is my formula:
="1. "&""&E21&" "&F21&" 2. "&E29&" "&F29&" 3. "&E33&"
"&F33&" 4. "&E37&" "&F37&" 5. "&E44&" "&F44&" 6a. "&E51&"
"&F51&" 6b. "&E55&" "&F55&" 6c. "&E59&""&F59&" 6d.
"&E63&" "&F63&" 7. "&E67&""&F67&" 8. "&E71&" "&F71&" 9.
"&E75&" "&F75&" 10a. "&E82&" "&F82&" 11. "&E87&" "&F87&"
12. "&E91&" "&F91&" 13. "&E97&" "&F97&" 14. "&E107&"
"&F107&" 15. "&E111&" "&F111&" 16. "&E115&""&F115&" 17.
"&E119&" "&F119&" 18a. "&E126&" "&F126&" 19. "&E130&"
"&F130&" 20. "&E142&" "&F142&" 21a. "&E149&" "&F149&" 22.
"&E155&" "&F155&" 23. "&E165&" "&F165&" 24a. "&E178&"
"&F178&" 25a. "&E185&" "&F185&" 25c. "&E194&" "&F194&"
26a. "&E203&" "&F203&" 26b. "& E207 &" "&F207 &" 27. "& E211&"
"&F211&" 28a. "& E215&" "& F215&" 29. "&E222 &" "& F222&"
29a. "&E226&" "& F226

Thanks!- Hide quoted text -

- Show quoted text -

Thanks but I also want the info that is put into E21 and F21 to be
labeled 1 and E29 and F29 to be 2 and so on. How would you write the
code for that because here it is putting E21 as 1 and F21 as 2?

Maggie

Maggie- based on your response, it sounds like you have two cells that
should be combined together; if so, just change the base formula to something
more like:

if((E21 & F21)<>"","1. "& E21 & F21 & ", ","")
This basically checks to see of E21 and F21 together have any content. My
apologies, I missed that you were combining two cells at a time instead of
having a 1-to-1 relationship between cells and numbering.

From this, just expand the formula using the info in my previous post,
replacing the E21 and F21 with each set of cell values that is important to
you.

HTH,
Keith

 

[Maggie]

Well, based on your formula it looks like you are going with a brute force
solution. Here are a few thoughts;
(1) To avoid having the extra numbers show up, you will have to determine
whether each cell is empty. Your base formula for each cell, instead of
simply "1. "& E21... you will have an if statement, something like:
if(E21<>"","1. "& E21 & ", ","")
This checks to see if the cell is blank, if not it puts together the output
for just that one cell, otherwise, it returns a zero-length string. Note that
it adds a comma (and space) on the back of whatever it returns, to make it
easier to string multiple cells together.
Now you (may) have two problems when you string multiple statements
together; first, I don't use XL2007, but in 2003 and earlier I believe you
may run into limits on the number of conditions that can be in a formula, or
the overall length of the formula in one cell. Not to worry- if needed, you
can build pieces of the combined formula (below) in multiple hidden columns,
and then use your overall output cell to pull together those pieces. The
other problem is that you will have an extra comma on the end of youroutput,
but I'll discuss that below.
So now you may have a cell [AA12] with something like:
[AA12]=Concatenate(if(E21<>"","1. "&E21,""),if(F21<>"","2.
"&F21,""),if(G21<>"","3. "&G21,""),if(H21<>"","4. "&H21,""),if(I21<>"","5..
"&I21,""))
and another cell [AB12] with
[AB12]=Concatenate(if(J21<>"","6. "&J21,""),if(K21<>"","7.
"&K21,""),if(L21<>"","8. "&L21,""),if(M21<>"","9. "&M21,""),if(N21<>"","10.
"&N21,""))
Let's say that the results in the first cell ends up being:
1. Missing file,
And the second cell ends up as:
6. No Coverage,
So your final cell could just be
=concatenate (AA21, AB21)
and would show
1. Missing file, 6. No Coverage,
The problem is that you have a trailing comma, which may be distracting. If
you knew you had at least one item on every line, it would be easy touse a
=Left statement to clean it up by removing just the last two characters;
= Left(concatenate (AA21, AB21),len(concatenate (AA21, AB21)-2)
this would pull off the comma and the final space. However, if you have rows
that won't have any comments at all, you'll need another IF statementto make
sure that the final value has at least 2 characters before you try toremove
the last two (otherwise you will get an error)
=IF(Len(concatenate (AA21, AB21))>2, Left(concatenate (AA21,
AB21),len(concatenate (AA21, AB21)-2),"")
This is all aircode (well, air-formula, anyway), but it should put you on
the right path.
HTH,
Keith
:
Maggie- this could be done (with brute force) using straight formulas, but
I'm assuming that you are looking for a VBA solution.
Will all records have at least one "problem" to be on your list, or will
there be rows where there are no problems/information, and you want A20 to
remain blank?
:
I have a worksheet where one cell is basically where information is
dumped into it from another cell.  I want to have the cell that has an
issue be numbered 1,2,3 and be dumped cell with the numbers.  Is there
a way to do that?
For example:
Cell A1 has Missing file
Cell A6 has No Coverage
Cell A20 reads 1. Missing file. 6. No Coverage.- Hide quoted text -
- Show quoted text -
There will be blanks in some of the fields.  I already have this
formula but I dont like that when something is not entered into the
cell the number still remains in the B232 cell.  I only want the
numbers present when data is entered into the cell.  Does that make
sense?
Here is my formula:
="1. "&""&E21&" "&F21&"     2. "&E29&" "&F29&"     3. "&E33&"
"&F33&"     4. "&E37&" "&F37&"     5. "&E44&" "&F44&"     6a. "&E51&"
"&F51&"     6b. "&E55&" "&F55&"     6c. "&E59&""&F59&"      6d.
"&E63&" "&F63&"      7. "&E67&""&F67&"     8. "&E71&" "&F71&"     9.
"&E75&" "&F75&"     10a. "&E82&" "&F82&"     11. "&E87&"  "&F87&"
12. "&E91&" "&F91&"     13. "&E97&" "&F97&"     14. "&E107&"
"&F107&"     15. "&E111&" "&F111&"     16. "&E115&""&F115&"    17.
"&E119&" "&F119&"     18a. "&E126&" "&F126&"     19. "&E130&"
"&F130&"     20. "&E142&" "&F142&"     21a. "&E149&" "&F149&"     22.
"&E155&" "&F155&"     23. "&E165&" "&F165&"     24a. "&E178&"
"&F178&"     25a. "&E185&" "&F185&"     25c. "&E194&" "&F194&"
26a. "&E203&" "&F203&"     26b. "& E207 &" "&F207 &"     27.. "& E211&"
"&F211&"     28a. "& E215&" "& F215&"     29. "&E222 &" "& F222&"
29a. "&E226&" "& F226
Thanks!- Hide quoted text -
- Show quoted text -

Thanks but I also want the info that is put into E21 and F21 to be
labeled 1 and E29 and F29 to be 2 and so on.  How would you write the
code for that because here it is putting E21 as 1 and F21 as 2?

Maggie

Maggie- based on your response, it sounds like you have two cells that
should be combined together; if so, just change the base formula to something
more like:

if((E21 & F21)<>"","1. "& E21 & F21 & ", ","")
This basically checks to see of E21 and F21 together have any content. My
apologies, I missed that you were combining two cells at a time instead of
having a 1-to-1 relationship between cells and numbering.

From this, just expand the formula using the info in my previous post,
replacing the E21 and F21 with each set of cell values that is important to
you.

HTH,
Keith- Hide quoted text -

- Show quoted text -

Okay, I got the formula to work but when I put my value into my final
cell nothing shows up. My formula is
=concatenate(C277,D277,E278,F278,G278,H278)
what am I doing wrong here?

 

[Maggie]

Well, based on your formula it looks like you are going with a brute force
solution. Here are a few thoughts;
(1) To avoid having the extra numbers show up, you will have to determine
whether each cell is empty. Your base formula for each cell, instead of
simply "1. "& E21... you will have an if statement, something like:
if(E21<>"","1. "& E21 & ", ","")
This checks to see if the cell is blank, if not it puts together the output
for just that one cell, otherwise, it returns a zero-length string.Note that
it adds a comma (and space) on the back of whatever it returns, to make it
easier to string multiple cells together.
Now you (may) have two problems when you string multiple statements
together; first, I don't use XL2007, but in 2003 and earlier I believe you
may run into limits on the number of conditions that can be in a formula, or
the overall length of the formula in one cell. Not to worry- if needed, you
can build pieces of the combined formula (below) in multiple hiddencolumns,
and then use your overall output cell to pull together those pieces.. The
other problem is that you will have an extra comma on the end of your output,
but I'll discuss that below.
So now you may have a cell [AA12] with something like:
[AA12]=Concatenate(if(E21<>"","1. "&E21,""),if(F21<>"","2.
"&F21,""),if(G21<>"","3. "&G21,""),if(H21<>"","4. "&H21,""),if(I21<>"","5..
"&I21,""))
and another cell [AB12] with
[AB12]=Concatenate(if(J21<>"","6. "&J21,""),if(K21<>"","7.
"&K21,""),if(L21<>"","8. "&L21,""),if(M21<>"","9. "&M21,""),if(N21<>"","10.
"&N21,""))
Let's say that the results in the first cell ends up being:
1. Missing file,
And the second cell ends up as:
6. No Coverage,
So your final cell could just be
=concatenate (AA21, AB21)
and would show
1. Missing file, 6. No Coverage,
The problem is that you have a trailing comma, which may be distracting. If
you knew you had at least one item on every line, it would be easy to use a
=Left statement to clean it up by removing just the last two characters;
= Left(concatenate (AA21, AB21),len(concatenate (AA21, AB21)-2)
this would pull off the comma and the final space. However, if you have rows
that won't have any comments at all, you'll need another IF statement to make
sure that the final value has at least 2 characters before you try to remove
the last two (otherwise you will get an error)
=IF(Len(concatenate (AA21, AB21))>2, Left(concatenate (AA21,
AB21),len(concatenate (AA21, AB21)-2),"")
This is all aircode (well, air-formula, anyway), but it should put you on
the right path.
HTH,
Keith
:
Maggie- this could be done (with brute force) using straight formulas, but
I'm assuming that you are looking for a VBA solution.
Will all records have at least one "problem" to be on your list, or will
there be rows where there are no problems/information, and you want A20 to
remain blank?
:
I have a worksheet where one cell is basically where information is
dumped into it from another cell.  I want to have the cell that has an
issue be numbered 1,2,3 and be dumped cell with the numbers.  Is there
a way to do that?
For example:
Cell A1 has Missing file
Cell A6 has No Coverage
Cell A20 reads 1. Missing file. 6. No Coverage.- Hide quoted text -
- Show quoted text -
There will be blanks in some of the fields.  I already have this
formula but I dont like that when something is not entered into the
cell the number still remains in the B232 cell.  I only want the
numbers present when data is entered into the cell.  Does that make
sense?
Here is my formula:
="1. "&""&E21&" "&F21&"     2. "&E29&" "&F29&"     3. "&E33&"
"&F33&"     4. "&E37&" "&F37&"     5. "&E44&" "&F44&"    6a. "&E51&"
"&F51&"     6b. "&E55&" "&F55&"     6c. "&E59&""&F59&"      6d.
"&E63&" "&F63&"      7. "&E67&""&F67&"     8. "&E71&" "&F71&"     9.
"&E75&" "&F75&"     10a. "&E82&" "&F82&"     11. "&E87&"  "&F87&"
12. "&E91&" "&F91&"     13. "&E97&" "&F97&"     14. "&E107&"
"&F107&"     15. "&E111&" "&F111&"     16. "&E115&""&F115&"     17.
"&E119&" "&F119&"     18a. "&E126&" "&F126&"     19. "&E130&"
"&F130&"     20. "&E142&" "&F142&"     21a. "&E149&" "&F149&"     22.
"&E155&" "&F155&"     23. "&E165&" "&F165&"     24a. "&E178&"
"&F178&"     25a. "&E185&" "&F185&"     25c. "&E194&" "&F194&"
26a. "&E203&" "&F203&"     26b. "& E207 &" "&F207 &"     27. "& E211&"
"&F211&"     28a. "& E215&" "& F215&"     29. "&E222 &" "& F222&"
29a. "&E226&" "& F226
Thanks!- Hide quoted text -
- Show quoted text -
Thanks but I also want the info that is put into E21 and F21 to be
labeled 1 and E29 and F29 to be 2 and so on.  How would you write the
code for that because here it is putting E21 as 1 and F21 as 2?
Maggie

Maggie- based on your response, it sounds like you have two cells that
should be combined together; if so, just change the base formula to something
more like:

if((E21 & F21)<>"","1. "& E21 & F21 & ", ","")
This basically checks to see of E21 and F21 together have any content. My
apologies, I missed that you were combining two cells at a time insteadof
having a 1-to-1 relationship between cells and numbering.

From this, just expand the formula using the info in my previous post,
replacing the E21 and F21 with each set of cell values that is important to
you.

HTH,
Keith- Hide quoted text -

- Show quoted text -

Okay, I got the formula to work but when I put my value into my final
cell nothing shows up. My formula is
=concatenate(C277,D277,E278,F278,G278,H278)
what am I doing wrong here?- Hide quoted text -

- Show quoted text -

Nevermind I figured what was wrong, but now I am getting an error with
the formula to take out the comma and space. I get a #Name? when I
enter the formula. Here is my formula: =IF(LEN(CONCATENATE
(C277,D277,E278,F278,G278,H278))>2, LEFT(CONCATENATE
(C277,D277,E278,F278,G278,H278),LEN(concatenate
(C277,D277,E278,F278,G278,H278)-2)))
My excell will not take the )-2),"") what am I doing wrong?

 

[ker_01]

Maggie- I believe it is just misplaced parantheses. When I simplify your
formula to make it more readable for troubleshooting by breaking out the
three components of the IF statement, I get:

=IF(
LEN(CONCATENATE(C277))>2,
LEFT(CONCATENATE(C277),LEN(concatenate(C277)-2))
[?]
)

The second statement is trying to subtract 2 from the concatenated string,
instead of the length of that string.

I think you want is:

=IF(
LEN(CONCATENATE(C277))>2, [this part is ok]
LEFT(CONCATENATE(C277),LEN(concatenate(C277))-2), [moved a paranthesis,
added comma]
"" [added null-length string as the third statement]
)

This is aircode, but hopefully that points you in the right direction.
HTH,
Keith

 

[Maggie]

Maggie- I believe it is just misplaced parantheses. When I simplify your
formula to make it more readable for troubleshooting by breaking out the
three components of the IF statement, I get:

=IF(
LEN(CONCATENATE(C277))>2,
LEFT(CONCATENATE(C277),LEN(concatenate(C277)-2))
[?]
)

The second statement is trying to subtract 2 from the concatenated string,
instead of the length of that string.

I think you want is:

=IF(
LEN(CONCATENATE(C277))>2,  [this part is ok]
LEFT(CONCATENATE(C277),LEN(concatenate(C277))-2), [moved a paranthesis,
added comma]
"" [added null-length string as the third statement]
)

This is aircode, but hopefully that points you in the right direction.
HTH,
Keith

Nevermind I figured what was wrong, but now I am getting an error with
the formula to take out the comma and space.  I get a #Name? when I
enter the formula.  Here is my formula:  =IF(LEN(CONCATENATE
(C277,D277,E278,F278,G278,H278))>2, LEFT(CONCATENATE
(C277,D277,E278,F278,G278,H278),LEN(concatenate
(C277,D277,E278,F278,G278,H278)-2)))
My excell will not take the )-2),"") what am I doing wrong?- Hide quoted text -

- Show quoted text -

Thanks it works now, but it still shows the comma and the space. Is
there a way to get rid of the comma?