question related to merging multiple statements

[Dharmesh]

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

 

[Mike H]

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(C11)=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.

 

[Jacob Skaria]

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)

 

[Rick Rothstein]

Here is another method for you to consider...

=A1&SUBSTITUTE(TRIM(RIGHT(B1,4)&" "&RIGHT(D1,4))," ",",")&C1

 

[Dharmesh]

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 !

 

[Rick Rothstein]

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