Extract All 9's from A1:A1000

[mgesh2002]

Can someone suggest me a code to extract number 9 from the range A1:A1000?

Numbers in Column A:

A1 = 1

A2 = 2

A3 = 3... and so on

A1000 = 1000


Now, I am trying to extract all the 9's from Column "A" and paste in Column "B" on the next respective cell.

Eg:

A199 = 199

Expected Result:

B199 = 99

Hope I am clear with my question.


Thanks,

Murugesh

 

[Luke M]

This will do it:

=REPT(9,LEN(A1)-LEN(SUBSTITUTE(A1,9,"")))

 

[Hui]

=REPT(9,LEN(A1)-LEN(SUBSTITUTE(A1,9,"")))

Dang, Luke beat me by 7 min's

 

[jason]

Luke does that often..... must be the ninja suit.

 

[mgesh2002]

That's Really Awesome! Superb!