Help with Macro to Concatenate Multiple Columns Skipping Blanks

[AlexOdou]

Dear All,

I need help creating a macro to concatenate multiples columns into one, skipping the blanks and removing the extra characters.

I'm using a formula like: =A1&"-"&B1, but if B1 is blank it will give me A1-

I receive a daily excel file with 5, 000 rows and multiple columns that need to be combine, using this formula if the cell is blank the character gets in the cell.

I don't know if i'm explaining this right, but i added an excel file with a sample:

in sheet 1 is how i received it and in sheet2, the first table is how it is produced with the above formula and the table 2 is the expected result.

any help or advice is highly appreciated.

Respectfully,

Alex

[Mumps1]

Place this formula in cell C4 of Sheet2: =SUBSTITUTE(TRIM(Sheet1!F3&" "&Sheet1!G3&" "&Sheet1!H3&" "&Sheet1!I3)," ",", ")
Change to suit your needs for the other cells.

[Roel Jongman]

I dont think you need VBA with OFFICE 365 you have available the TEXTJOIN function that can eighter exclude or include blank cells in a concatantion

Please Login or Register  to view this content.

I have applied the function to column A and B of sheet2. in the attachement
also.. have a look at cell C2 of sheet1, that seems to have an odd formula with an extra comma in it that might confuse your results, for presenting my solution I removed the formula

[AlexOdou]

Hi Mumps1,

Thank you very much for your help on this matter, your formula work, but in some cases when the cell had two words it is adding a coma in between.
Like: Foreign Body = Foreign, Body, and Superior Temporal =Superior, Temporal please see the columns in yellow columns F and D.

Also, in column D the separator between the two numbers should be an x and a coma for the unit, like 2x2, cm

see the attachement.

Thank you very much in advance for your time, this is a huge help!

Respectfully,

Alex

[AlexOdou]

Hi, Jongman,

Thank you very much for your time, the reason I'm looking for a macro is that the original file have more than 5000 row and around 400 columns.

I try the TEXTJOIN function but i was not able to make it work, but from your file when i copy paste the function it give me the error code #Name?

Again, tank you for your time on helping me on this matter.

Respectfully,

Alex

[jindon]

UDF

Use in cell like
in Sheet2 B2
=SUBSTITUTE(TRIM(HTextJoin(Sheet1!C2:E2," "))," ",", ")

Please Login or Register  to view this content.

[AlexOdou]

Hi Jindon,

Thank you very much for your replay to my question, I really appreciate it!

i might be doing something wrong, when using the formula it returns #NAME Error.

how to use this function?, I'm not to familiar with funcitons.


Thanks again!

Respectfully,

Alex

[jindon]

See the attached.

[AlexOdou]

Jindon,

Thank you very much, it work perfectly when the cell contain one word only, but there are cells with two or more words like in sheet 1 E3 Foreign Body, the result is coming as Foreign, Body.
can you modify the function so it will take the cell content (concatenating text insted)

Thank you in advance for your invaluable help!

Respectfully

Alex

[jindon]

Then just change fotmula to

=HTextJoin(range,", ")
where range should be appropreate.

[AlexOdou]

Hi Jindon,

It work perfectly!!

One more thing, there are columns like the one in sheet 1 from P to Q where i have to joint P and Q with and X and PQ to R with a coma Like 2x2, cm
it's possible to modify it to satisfy this requirement.

I appreciate your help!

Sincerely,

Alex

[jindon]

In that case function should be changed,
Use in cell like

=StrJoin(", ",A1:C1,D2:F2,G1)
where ", " is a joining string
A1:C1,D2:F2,G1 are the range/array/string that need to be concatenated.

You could use it like
=StrJoin(", ",A1:G1,{"abc",123},"Hello")

Please Login or Register  to view this content.

[AlexOdou]

Jindon,

Sorry, I think I confuse you.

the data to concatenate are Column P=Numeric value, "x", Column Q=Numeric Value, "," Column R= measurement unit.

Column P Column Q Column R Result
2 2 cm 2x2, cm
3 4 mm 3x4, mm
5 2 um 5x2, um

As you see, column R is not a single value that we can use as string, it can be any measurement unit, but can also be blank.

Hope this is clear.

Respectfully,


Alex

[AlexOdou]

[table="width: 500"]
[tr]
[td]DM1 DM2 DMU Result
1 2 mm 1x2, mm
2 3 cm 2x3, cm
4 5 um 4x5 um

[jindon]

How do you want the result when one/two or all column(s) is/are blank?

[AlexOdou]

Jindon,

If all columns are blank, I want an empty result cell, if one column is blank, the column should be ignore and the connector, but only DM2 and or DMU can be blank

Example: Column DM1 DM2 DMU Result
1 cm 1, cm
2 3 2x3

Thank you very much for your help!!

Respectfully,

Alex

[jindon]

Change the function to below and use in cell like

=HTextJoin(Sheet1!P2:R2,"",TRUE,"x")

Please Login or Register  to view this content.

[AlexOdou]

Jindon,

You nailed it!!

The function work perfectly!

You save me a lot of work, Thank you so very much for your help. I really appreciate it

Respectfully,

Alex

[AlexOdou]

Jindon,

Just one more detail, when I try to add the function using a macro it's giving me a Run-Time Error code 1004, Application-Define or Object-Define Error.


Thank you!

Respectfully,

Alex

[jindon]

Just one more detail, when I try to add the function using a macro it's giving me a Run-Time Error code 1004, Application-Define or Object-Define Error.

How are you adding the formula?

[AlexOdou]

Jindon,

This is what i came out to enter the functions into the fields using this macro. it enter the funcitons, then copy the whole section and paste the values in the range.

Please Login or Register  to view this content.

Thank you for your invaluable help!

Respectfully,

Alex

[jindon]

You need to add extra double quote " for each one of them

Please Login or Register  to view this content.

[AlexOdou]

Great!

was not aware of that.

Thank you again.

Respectfully,

Alex

[AlexOdou]

Jindon,

is there any way to enter the formula in a dynamic range?

The weekly reports I receive have a different number of rows, around 5000 and want my macro to enter the formula in all applicable cells without changing the range accordingly.


Thanks

Alex

[jindon]

my guess

Please Login or Register  to view this content.

[AlexOdou]

Jindon,

It work!

Thanks a lot.

Alex