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