Removing unwanted commas

[fluffynicesheep]

Hi,

I currently have the following formula that looks at 3 cells and then pulls through up to 3 codes into another cell using a VLOOKUP and CONCATENATE function.

The codes get pulled through fine, however if there are empty cells or the Vlookup should pull through a blank, then it's just showing commas.

Is there any way that the formula below could be re-written to only pull through commas if a number is pulled through (so it goes between two numbers)

=IF(a_Main!BN2="","",IF(a_Main!DE2="",CONCATENATE(VLOOKUP(a_Main!BN2,'Lookups'!$AH$10:$AI$33,2,FALSE),",",VLOOKUP(a_Main!BO2,'Lookups'!$AH$10:$AI$33,2,FALSE),),CONCATENATE(VLOOKUP(a_Main!BN2,'Lookups'!$AH$10:$AI$33,2,FALSE),",",VLOOKUP(a_Main!BO2,'Lookups'!$AH$10:$AI$33,2,FALSE),",",VLOOKUP(a_Main!DE2,'Lookups'!$AH$10:$AI$33,2,FALSE))))


Many thanks

 

[Joyner]

Hello, what version of Office / Excel are you using? Do you have the CONCAT functions?

For the result that you pull in, is it a number or text and if text can it have spaces in it>

I assume if it pulls as three results it should look like this - Result, Result, Result

How else can the results be? For example:

For no results does it show just two commas ( ,, )

For one result, can it look like - Result,, or ,,result

Two results, Result, Result, or , Result, Result, or Result, , Result

If those are the only possibilities, we can deal with that, but no great easy way I am aware of without CONCAT.

 

[fluffynicesheep]

Thanks for the repy.

I am currently using Excel 2010.

Basically in this new cell I need to list ALL disabilities / learning difficulties that someone has.

Cell BN2 contains the primary disability (text format)
Cell BO2 contains the learning difficulty (text format)
Cell DE2 contains the secondary disability (text format)

Cells BN2 and BO2 will either state the disability or it will state "No disability" and "No Learning Difficulty"
Cell DE2 will either state the name of the 2nd disability or will be left blank

The vlookups (as in the email) will report back a 1 digit number for each response - e.g. Aspergers = 1, Mental health Issue = 2 etc - No disability will be blank as will no learning difficulty

Therefore:

Option 1) The cell is empty ... this means that the response hasn't been put in at all - so the cell will stay blank

Option 2) There is something in the primary disability (could be either the name of the disability itself or it could state "no disability") - so we want to report back the result of this vlookup PLUS the vlookup from learning difficulties in cell BO2 (again could be a named difficulty or "No Difficulty"). If either cell states "No disabiity" or "No learning difficulty" - it will come back blank - so this field is currently looking like: blank (because of no disability), 2 (lookup learning difficulty) or 3 (lookup disability) ,blank (no learning difficulty) or blank (no disability) ,blank (no learning difficulty) or 3 (named disability),4 (named learning difficulty) - so we need to get rid of the commas by the blanks

Option 3) The final option is that a secondary disability has now been declared (cell DE2 is no longer blank) - so now we have the requirement to vlookup from 3 fields. The 3rd one will always have number in, but again the first 2 vlookups could either be blank or have a number. So it could be 4,3,2 or 4,2 or ,,2 etc


I hope this gives you an idea of what I need, and If we can find a way to get rid of the commas when blanks appear that would be fantastic!

Thanks

 

[Joyner]

Hello, I am not sure I follow a couple things but try this version:

 =SUBSTITUTE(TRIM(SUBSTITUTE(IF(a_Main!BN2="","",IF(a_Main!DE2="",CONCATENATE(VLOOKUP(a_Main!BN2,'Lookups'!$AH$10:$AI$33,2,FALSE),",",VLOOKUP(a_Main!BO2,'Lookups'!$AH$10:$AI$33,2,FALSE),),CONCATENATE(VLOOKUP(a_Main!BN2,'Lookups'!$AH$10:$AI$33,2,FALSE),",",VLOOKUP(a_Main!BO2,'Lookups'!$AH$10:$AI$33,2,FALSE),",",VLOOKUP(a_Main!DE2,'Lookups'!$AH$10:$AI$33,2,FALSE)))),","," "))," ",",")

formula from MARK W. here:

https://www.mrexcel.com/forum/excel-questions/2086-stripping-leading-trailing-commas.html

This is with no space after the comma (I think that is what you wanted your formula to do), if you want a space after the comma, add a space after the last comma in the formula.

 

[Joyner]

I realize now that this will replace a space between words like "No learning difficulty" with a comma, like this "No,learning,difficulty"

So back to the drawing board for now to design something more robust.

Can you change things up a bit to have the VLOOKUP results posted to separate cells and then concatenate the results> That would be easier to handle then with some other code / UDF to put it all together.

 

[Joyner]

Some solutions in one single formula can get pretty long unless you return the individual VLOOKUPs in separate cells as I ask above, but here is one solution, it involves adding to what I did above, plus adding another SUBSTITUTE formula to each VLOOKUP to add a pipe "|" to the spaces in the vlookup results. So like this, but untested:

 =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(IF(a_Main!BN2="","",IF(a_Main!DE2="",CONCATENATE(SUBSTITUTE(VLOOKUP(a_Main!BN2,Lookups!$AH$10:$AI$33,2,FALSE)," ","|"),",",SUBSTITUTE(VLOOKUP(a_Main!BO2,Lookups!$AH$10:$AI$33,2,FALSE)," ","|"),),CONCATENATE(SUBSTITUTE(VLOOKUP(a_Main!BN2,Lookups!$AH$10:$AI$33,2,FALSE)," ","|"),",",SUBSTITUTE(VLOOKUP(a_Main!BO2,Lookups!$AH$10:$AI$33,2,FALSE)," ","|"),",",SUBSTITUTE(VLOOKUP(a_Main!DE2,Lookups!$AH$10:$AI$33,2,FALSE)," ","|")))),","," "))," ",",   "),"|"," ")

May need to add TRIM to the individual VLOOKUP results if there can be leading, trailing or extra spaces in the results.

 

[fluffynicesheep]

Many thanks for looking at this...

I'll see how it looks when I'm next at the pc.

Once again thanks for your help, and I'll let you know how it looks soon.

 

[fluffynicesheep]

Hi,

This is excellent - it works!! - thanks so much for your help!!

 

[Joyner]

You are welcome, glad it helped.