|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 26th, 2010, 12:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
question related to merging multiple statements
DEAR MR. /MS.
I HAVE TWO NUMBERS 4565845 and 1543295 AND A TEXT "ALSO WORKING ECM IN Q 201" I WANT TO MERGE THE RIGHT 4 DIGITS FROM BOTH THE NUMBERS WITH THE TEXT SEPARATED BY A COMMA. EXAMPLE-: ALSO WORKING ECM 5845, 3295 IN Q 201 I PLACED 4565845 IN CELL C1 AND 1543295 IN CELL D2. I PLACED THE TEXT "ALSO WORKING ECM" IN CELL A1 AND “IN Q 201" IN CELL B2. AND USED THE FOLLOWING FORMULA =CONCATENATE (A1, RIGHT (C1, 4),",", RIGHT (D1, 4)," ", B1) BUT WHEN I DELETE ONE NUMBER FROM THE COLUMN, THE FIRST NUMBER CARRIES THE ",". FOR EXAMPLE- IF I DELETE 1543295 FROM THE CELL D2, THE FORMULA DISPLAYS AS "ALSO WORKING ECM 5845,” FOLLOWED BY A COMMA. I DONT' WANT THIS COMMA. IF I DELETE ONE NUMBER I WANT THE TEXT TO BE DISPLAYED AS "ALSO WORKING ECM 5845 IN Q 201" AND IF I PLACE ANY NUMBER IN COLUMN, IT SHOULD DISPLAY AS "ALSO WORKING ECM 5845, 3295 IN Q 201" PLEASE SIR/MAM, HELP ME OUT! I HAVE TRIED ALMOST ALL THE WAYS TRYING TO FIND OUT THE SOLUTION OF THIS PROBLEM. I WOULD BE HIGHLY THANKFUL TO YOU! HOPE TO HEAR SOON FROM YOU ! DHARMESH 
|
|
#2
April 26th, 2010, 12:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
question related to merging multiple statements
Hi,
First I think you meay have a problem with the keyboard. Typing in caps makes what you have written very difficult to read and is considered rude. i.e shouting. Try this which assumes there will only be numbers in C1 & d1 =A1&RIGHT(C1,4)&IF(COUNT(C1  1)=2,",","")&RIGHT(D1 ,4)-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Dharmesh" wrote: DEAR MR. /MS. I HAVE TWO NUMBERS 4565845 and 1543295 AND A TEXT "ALSO WORKING ECM IN Q 201" I WANT TO MERGE THE RIGHT 4 DIGITS FROM BOTH THE NUMBERS WITH THE TEXT SEPARATED BY A COMMA. EXAMPLE-: ALSO WORKING ECM 5845, 3295 IN Q 201 I PLACED 4565845 IN CELL C1 AND 1543295 IN CELL D2. I PLACED THE TEXT "ALSO WORKING ECM" IN CELL A1 AND “IN Q 201" IN CELL B2. AND USED THE FOLLOWING FORMULA =CONCATENATE (A1, RIGHT (C1, 4),",", RIGHT (D1, 4)," ", B1) BUT WHEN I DELETE ONE NUMBER FROM THE COLUMN, THE FIRST NUMBER CARRIES THE ",". FOR EXAMPLE- IF I DELETE 1543295 FROM THE CELL D2, THE FORMULA DISPLAYS AS "ALSO WORKING ECM 5845,” FOLLOWED BY A COMMA. I DONT' WANT THIS COMMA. IF I DELETE ONE NUMBER I WANT THE TEXT TO BE DISPLAYED AS "ALSO WORKING ECM 5845 IN Q 201" AND IF I PLACE ANY NUMBER IN COLUMN, IT SHOULD DISPLAY AS "ALSO WORKING ECM 5845, 3295 IN Q 201" PLEASE SIR/MAM, HELP ME OUT! I HAVE TRIED ALMOST ALL THE WAYS TRYING TO FIND OUT THE SOLUTION OF THIS PROBLEM. I WOULD BE HIGHLY THANKFUL TO YOU! HOPE TO HEAR SOON FROM YOU ! DHARMESH
|
|
#3
April 26th, 2010, 12:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
question related to merging multiple statements
Replace "," with the formula
=IF(COUNT(C1,D1)=2,",","") Something like =CONCATENATE(A1,RIGHT(C1,4),IF(COUNT(C1,D1)=2,",", ""),RIGHT(D1,4)," ",B1) -- Jacob (MVP - Excel) "Dharmesh" wrote: DEAR MR. /MS. I HAVE TWO NUMBERS 4565845 and 1543295 AND A TEXT "ALSO WORKING ECM IN Q 201" I WANT TO MERGE THE RIGHT 4 DIGITS FROM BOTH THE NUMBERS WITH THE TEXT SEPARATED BY A COMMA. EXAMPLE-: ALSO WORKING ECM 5845, 3295 IN Q 201 I PLACED 4565845 IN CELL C1 AND 1543295 IN CELL D2. I PLACED THE TEXT "ALSO WORKING ECM" IN CELL A1 AND “IN Q 201" IN CELL B2. AND USED THE FOLLOWING FORMULA =CONCATENATE (A1, RIGHT (C1, 4),",", RIGHT (D1, 4)," ", B1) BUT WHEN I DELETE ONE NUMBER FROM THE COLUMN, THE FIRST NUMBER CARRIES THE ",". FOR EXAMPLE- IF I DELETE 1543295 FROM THE CELL D2, THE FORMULA DISPLAYS AS "ALSO WORKING ECM 5845,” FOLLOWED BY A COMMA. I DONT' WANT THIS COMMA. IF I DELETE ONE NUMBER I WANT THE TEXT TO BE DISPLAYED AS "ALSO WORKING ECM 5845 IN Q 201" AND IF I PLACE ANY NUMBER IN COLUMN, IT SHOULD DISPLAY AS "ALSO WORKING ECM 5845, 3295 IN Q 201" PLEASE SIR/MAM, HELP ME OUT! I HAVE TRIED ALMOST ALL THE WAYS TRYING TO FIND OUT THE SOLUTION OF THIS PROBLEM. I WOULD BE HIGHLY THANKFUL TO YOU! HOPE TO HEAR SOON FROM YOU ! DHARMESH
|
|
#4
April 26th, 2010, 07:14 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
question related to merging multiple statements
Here is another method for you to consider...
=A1&SUBSTITUTE(TRIM(RIGHT(B1,4)&" "&RIGHT(D1,4))," ",",")&C1 -- Rick (MVP - Excel) "Dharmesh" wrote in message ... DEAR MR. /MS. I HAVE TWO NUMBERS 4565845 and 1543295 AND A TEXT "ALSO WORKING ECM IN Q 201" I WANT TO MERGE THE RIGHT 4 DIGITS FROM BOTH THE NUMBERS WITH THE TEXT SEPARATED BY A COMMA. EXAMPLE-: ALSO WORKING ECM 5845, 3295 IN Q 201 I PLACED 4565845 IN CELL C1 AND 1543295 IN CELL D2. I PLACED THE TEXT "ALSO WORKING ECM" IN CELL A1 AND “IN Q 201" IN CELL B2. AND USED THE FOLLOWING FORMULA =CONCATENATE (A1, RIGHT (C1, 4),",", RIGHT (D1, 4)," ", B1) BUT WHEN I DELETE ONE NUMBER FROM THE COLUMN, THE FIRST NUMBER CARRIES THE ",". FOR EXAMPLE- IF I DELETE 1543295 FROM THE CELL D2, THE FORMULA DISPLAYS AS "ALSO WORKING ECM 5845,” FOLLOWED BY A COMMA. I DONT' WANT THIS COMMA. IF I DELETE ONE NUMBER I WANT THE TEXT TO BE DISPLAYED AS "ALSO WORKING ECM 5845 IN Q 201" AND IF I PLACE ANY NUMBER IN COLUMN, IT SHOULD DISPLAY AS "ALSO WORKING ECM 5845, 3295 IN Q 201" PLEASE SIR/MAM, HELP ME OUT! I HAVE TRIED ALMOST ALL THE WAYS TRYING TO FIND OUT THE SOLUTION OF THIS PROBLEM. I WOULD BE HIGHLY THANKFUL TO YOU! HOPE TO HEAR SOON FROM YOU ! DHARMESH
|
|
#5
May 1st, 2010, 12:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
question related to merging multiple statements
Thanks Sir! I tried it but I have the numbers stored as text and I cannot
covert them to numbers. The problem is that the formula you mentioned works with numbers and not with the numbers stored as text. It does not indentify comma as a separator. To be more precise-: I have the following numbers- 45658458458458 in a1 45485658459224 in a2 25458456584586 in a3 14548565865845 in a4 and the text "also working ecm" in b1 & "in q201" in b2 The numbers are stored as text. If I convert them to numbers they would look as the following numbers and actual numbers would be replaced. I cannot use Custom in Format Cells for some of the numbers would take "0" in the end. 4.56585E+13 4.54857E+13 2.54585E+13 1.45486E+13 Now, I am trying to merge the actual last four digits from each numbers stored as text separated with comma. And, I should be getting the formula Like, "also working ecm 8458,9224,4586,5845 in q201". If I remove any number like the last number 5845, I should get in return "also working ecm 8458,9224,4586 in q201" and If i remove the first number like 8458, I should be getting"also working ecm 9224,4586,5845 in q201" and I remove the third number like 4586, I should be getting "also working ecm 8458,9224,5845 in q201" without any extended space and comma. Kindly help ! "Mike H" wrote: Hi, First I think you meay have a problem with the keyboard. Typing in caps makes what you have written very difficult to read and is considered rude. i.e shouting. Try this which assumes there will only be numbers in C1 & d1 =A1&RIGHT(C1,4)&IF(COUNT(C1 1)=2,",","")&RIGHT(D1 ,4)-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Dharmesh" wrote: DEAR MR. /MS. I HAVE TWO NUMBERS 4565845 and 1543295 AND A TEXT "ALSO WORKING ECM IN Q 201" I WANT TO MERGE THE RIGHT 4 DIGITS FROM BOTH THE NUMBERS WITH THE TEXT SEPARATED BY A COMMA. EXAMPLE-: ALSO WORKING ECM 5845, 3295 IN Q 201 I PLACED 4565845 IN CELL C1 AND 1543295 IN CELL D2. I PLACED THE TEXT "ALSO WORKING ECM" IN CELL A1 AND “IN Q 201" IN CELL B2. AND USED THE FOLLOWING FORMULA =CONCATENATE (A1, RIGHT (C1, 4),",", RIGHT (D1, 4)," ", B1) BUT WHEN I DELETE ONE NUMBER FROM THE COLUMN, THE FIRST NUMBER CARRIES THE ",". FOR EXAMPLE- IF I DELETE 1543295 FROM THE CELL D2, THE FORMULA DISPLAYS AS "ALSO WORKING ECM 5845,” FOLLOWED BY A COMMA. I DONT' WANT THIS COMMA. IF I DELETE ONE NUMBER I WANT THE TEXT TO BE DISPLAYED AS "ALSO WORKING ECM 5845 IN Q 201" AND IF I PLACE ANY NUMBER IN COLUMN, IT SHOULD DISPLAY AS "ALSO WORKING ECM 5845, 3295 IN Q 201" PLEASE SIR/MAM, HELP ME OUT! I HAVE TRIED ALMOST ALL THE WAYS TRYING TO FIND OUT THE SOLUTION OF THIS PROBLEM. I WOULD BE HIGHLY THANKFUL TO YOU! HOPE TO HEAR SOON FROM YOU ! DHARMESH
|
|
#6
May 1st, 2010, 03:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
question related to merging multiple statements
Just noting that you changed your original question completely. It is always
a better idea to initially ask the actual question you want answered as opposed to trying to simplify it for us and then having to try and modify the answers you receive. With that said, I am wondering if your latest question is still the actual question you need answered. My concern is your change from two "numbers" to parse to four of them... unlimited concatenations cannot be built with simple formulas.... you have to include a term for each cell that will eventually need to be concatenated. Yes, you can have the formula display nothing for when the "number" cells contain nothing, but you can't go the other way without specifying a term for each item to be concatenated. So, assuming your latest post is what you actually want, here is the formula I posted elsewhere in this thread modified to handle the cells you now say have the "numbers" and text strings in as opposed to the cells you originally said contain them. =B1&" "&SUBSTITUTE(TRIM(RIGHT(A1,4)&" "&RIGHT(A2,4)&" "&RIGHT(A3,4)&" "&RIGHT(A4,4))," ",",")&" "&B2 -- Rick (MVP - Excel) "Dharmesh" wrote in message ... Thanks Sir! I tried it but I have the numbers stored as text and I cannot covert them to numbers. The problem is that the formula you mentioned works with numbers and not with the numbers stored as text. It does not indentify comma as a separator. To be more precise-: I have the following numbers- 45658458458458 in a1 45485658459224 in a2 25458456584586 in a3 14548565865845 in a4 and the text "also working ecm" in b1 & "in q201" in b2 The numbers are stored as text. If I convert them to numbers they would look as the following numbers and actual numbers would be replaced. I cannot use Custom in Format Cells for some of the numbers would take "0" in the end. 4.56585E+13 4.54857E+13 2.54585E+13 1.45486E+13 Now, I am trying to merge the actual last four digits from each numbers stored as text separated with comma. And, I should be getting the formula Like, "also working ecm 8458,9224,4586,5845 in q201". If I remove any number like the last number 5845, I should get in return "also working ecm 8458,9224,4586 in q201" and If i remove the first number like 8458, I should be getting"also working ecm 9224,4586,5845 in q201" and I remove the third number like 4586, I should be getting "also working ecm 8458,9224,5845 in q201" without any extended space and comma. Kindly help ! "Mike H" wrote: Hi, First I think you meay have a problem with the keyboard. Typing in caps makes what you have written very difficult to read and is considered rude. i.e shouting. Try this which assumes there will only be numbers in C1 & d1 =A1&RIGHT(C1,4)&IF(COUNT(C1 1)=2,",","")&RIGHT(D1 ,4)-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Dharmesh" wrote: DEAR MR. /MS. I HAVE TWO NUMBERS 4565845 and 1543295 AND A TEXT "ALSO WORKING ECM IN Q 201" I WANT TO MERGE THE RIGHT 4 DIGITS FROM BOTH THE NUMBERS WITH THE TEXT SEPARATED BY A COMMA. EXAMPLE-: ALSO WORKING ECM 5845, 3295 IN Q 201 I PLACED 4565845 IN CELL C1 AND 1543295 IN CELL D2. I PLACED THE TEXT "ALSO WORKING ECM" IN CELL A1 AND “IN Q 201" IN CELL B2. AND USED THE FOLLOWING FORMULA =CONCATENATE (A1, RIGHT (C1, 4),",", RIGHT (D1, 4)," ", B1) BUT WHEN I DELETE ONE NUMBER FROM THE COLUMN, THE FIRST NUMBER CARRIES THE ",". FOR EXAMPLE- IF I DELETE 1543295 FROM THE CELL D2, THE FORMULA DISPLAYS AS "ALSO WORKING ECM 5845,” FOLLOWED BY A COMMA. I DONT' WANT THIS COMMA. IF I DELETE ONE NUMBER I WANT THE TEXT TO BE DISPLAYED AS "ALSO WORKING ECM 5845 IN Q 201" AND IF I PLACE ANY NUMBER IN COLUMN, IT SHOULD DISPLAY AS "ALSO WORKING ECM 5845, 3295 IN Q 201" PLEASE SIR/MAM, HELP ME OUT! I HAVE TRIED ALMOST ALL THE WAYS TRYING TO FIND OUT THE SOLUTION OF THIS PROBLEM. I WOULD BE HIGHLY THANKFUL TO YOU! HOPE TO HEAR SOON FROM YOU ! DHARMESH
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
