Formula to remove all but the last of the same character in a cell

[James01]

Hi

I have data in cell A1 and A2 which looks like below

SECTOR - 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-530020,

In cell A1 the number of hypens (-) are 2
In cell A2 the number of - are 3

What I am looking for is a formula which can remove all additional - except the last one. Therefore the result of the formula should be

SECTOR 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30 15 171 DABA GARDENS, NEAR SARASWATI PARK,-530020

Is this possible by using a formula

Thanks for the replies

 

[pgc01]

Thank you for the clarification.

 

[James01]

Hi

Well on the other hand if from the whole list I want to identify the cell which do end with 6 digits, what formula should I use.

Thanks

 

[dscg]

Here's a working solution. There may be a shorter one, but this should work:

Excel Workbook
	A	B
1	SECTOR - 11, HIRAN MAGRI, - 313001	TRUE
2	MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-530020,	FALSE
3	MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-53000,	FALSE
4	MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-5300200,	FALSE
5	MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-5300200	FALSE
6	MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-53000	FALSE
7	MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-530020	TRUE
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A1,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A1,LEN(A1)-6,1)))))
B2	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A2,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A2,LEN(A2)-6,1)))))
B3	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A3,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A3,LEN(A3)-6,1)))))
B4	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A4,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A4,LEN(A4)-6,1)))))
B5	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A5,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A5,LEN(A5)-6,1)))))
B6	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A6,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A6,LEN(A6)-6,1)))))
B7	=AND(ISNUMBER(VALUE(RIGHT(SUBSTITUTE(A7,"-","§"),6))),NOT(ISNUMBER(VALUE(MID(A7,LEN(A7)-6,1)))))