joining two cells including commas

**jbkhan** · 04-14-2011, 10:27 AM

i have problem to joining two cells which also including commas like;

A B Result(C)
4 5,6 45
46
56 44,2 5644
562

like i have two column A and B, column A has a number joining with a two numbers of B column but separated with comma and the result should be the same like in Shown Above.

Anyone can help me about that.....

**NBVC** · 04-14-2011, 11:00 AM

Assuming your data starts in A2 and B2, try in C2:

=IF(A2<>"",A2&LEFT(B2,FIND(",",B2)-1),A1&MID(B1,FIND(",",B1)+1,255))+0

copied down.

**jbkhan** · 04-19-2011, 07:28 AM

Originally Posted by NBVC

Assuming your data starts in A2 and B2, try in C2:

=IF(A2<>"",A2&LEFT(B2,FIND(",",B2)-1),A1&MID(B1,FIND(",",B1)+1,255))+0

copied down.

The formula above is only working for the field having one comma, but here i have more than 50 commas in one column which i want to joining as well, so what should i do for that.....And also i have random data some fields have no commas and some fields have one commas and some of them more than 50 commas..........

**NBVC** · 04-19-2011, 07:42 AM

Can you give a better example then of what your column A and B look like now, and what you want the results to look like.

Post a sample workbook showing before and after.

**jbkhan** · 04-19-2011, 08:14 AM

Originally Posted by NBVC

Can you give a better example then of what your column A and B look like now, and what you want the results to look like.

Post a sample workbook showing before and after.

i have column A and Column B which i want to join like .......column A has a record of 93 or something else and column B has 456,345,234,7898 just like that and the result should be on C which look should be like given below.......
93456
93345
937898
93234
joining of two columns separated by commas

**NBVC** · 04-19-2011, 08:39 AM

So assuming data in A and B is set up as per your sample, with spacing between entries.... and assuming you can start in Row 2 (i.e. at A2), then add a helper column.

At C2 enter:

Please Login or Register  to view this content.

and copy down to the bottom. You can hide this column, if desired.

then at D2 enter:

Please Login or Register  to view this content.

and copy down.

**jbkhan** · 04-21-2011, 06:38 AM

sir here again problem coming whn i write the formula of =IF(ISNUMBER(A2),1,C1+1) the result is coming 1 and when i drag it, it only show 1 in all the cells and when i write the other formula then it will show the first digits before the commas and will not shows after the commas digits,,,like it will show

e.g: column A has 93 and column B has 555, 666, 777 thn it will combining only the first digits of both the column like 93555 and whn i drag it for the other 2 digits then it will show error like #Value

find also attachement of excel file name PRICE CHANGE.XLS....................

**jwright650** · 04-21-2011, 07:56 AM

maybe this?
place this in C1...and drag down

Please Login or Register  to view this content.

**jbkhan** · 04-21-2011, 08:11 AM

Originally Posted by jwright650

maybe this?
place this in C1...and drag down

Please Login or Register  to view this content.

sir actually i m joining these two columns, column A and B like column A has a record of 787 in one cell and column B has 777, 888, 9999 so these would be in one cell again and the result should be like in three rows in column C like.
787777
787888
7879999
to joining two columns separated by commas,,,the column A has no commas and the column B has more than 50 commas in one cells....so splitting these cells into rows by joining with the ColumnA.........

**NBVC** · 04-21-2011, 09:11 AM

My formulas were based on your original setup that you removed as I posted my solution.

It was based on you have placed the column A values at intervals equal to the number of comma separated values in column B.

I am not sure of an efficient, non-VBA solution right now....