How to ignore blank cells while concatenating multiple cell values
Hi,
I am trying to concatenate multiple cell values in one cell as below:
Column A Column B Column C Column D Column E Column F
Blank Text 1 Text 2 Blank Text 3 I am concatenating values from A to E
My formula is
A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E1
What I am getting in Column F is
Blank
Text 1
Text 2
Blank
Text 3
What I am looking for is
Text 1
Text 2
Text 3
Can anyone help me with the right formula?
Try it like this
Formula:
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi, Thanks for your quick reply. But, I am getting a totally blank Col F if I apply this formula. I am not able to attach my file, but my Column F is totally blank now after applying your formula.
Try this
Formula:Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
One way:
=A1 & B1 & C1
If any cells are blank, they'll be ignored.
Note that by "blank" I mean truly blank. If you or your users are in the habit of "blanking" a cell by using the space bar, the cell(s) actually contain space characters. To ignore those:
=IF(LEN(TRIM(A1))=0,"",A1) & IF(LEN(TRIM(B1))=0,"" B1) & IF(LEN(TRIM(C1))=0,"",C1)
or, depending on your particular circumstances, this might work just as well:
=TRIM(A1 & B1 & C1)
Do your text strings contain spaces, or are they ALWAYS a continuous string of characters?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There MUST be a simpler way than this...
Formula:Please Login or Register to view this content.
Don't forget to enable text wrapping. This removes double char 10s and any single char 10 at the start of the string.
This formula adds a "return" even if there is a gap in between the words in one cell. The result is
Text
1
Text
2
Text
3
I am expecting to get the result
Text 1
Text 2
Text 3
Who are you replying to????????
Sorry, that reply was to AlKey.
Glenn, your formula has come closer to my requirement. I am trying to attach a file which specifies what I am doing and I am looking for.
Glenn,
I have attached a file in my previous post and have given examples of what I am trying to do.
"Error List" column is where I am trying to collect the errors against each employee. I wanted to ignore the blank cells while collecting the errors.
There is no a perfect way of concatenating text strings with a formula unless you are a subscriber of Office 365 which offers a great excel function TEXTJOIN.
However, there is a good news! You can get the same functionality with a VBA version.
Here is how to do this:
1. Copy code below
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.
Don't forget to save your worbook as Macro-Enabled workbook.
http://www.get-digital-help.com/2016...tjoin-function
Then enter formula in a cell F1 and click on Wrap Text located on the Home tab and copy formula down if needed.
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
v A B C D E F 1 Text 1 Text 12 Text 2 Text 3 Text 1
Text 12
Text 2
Text 32 Text 1 Text 2 Text 3 Text 1
Text 2
Text 33 Text 1 Text 1
I would go with Alkey's UDF, for sure... A formula for so many columns would be horrible!!
Thanks AlKey. It's really working fine. I have added the formula =TEXTJOIN(CHAR(10),TRUE,A1:E1) in F1, but I get #VALUE! if there is no message (i.e. no text) for an employee in a row. How to avoid, i.e. how to make F1 blank if there is no message in a row or rows?
Try
=iferror(TEXTJOIN(CHAR(10),TRUE,A1:E1),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks