Looking to replace "dots"

[Rick]

Have a column of data containing from the left any number of dots
followed by a number.
The columns can contain anywhere from none, to up to 25 dots, and
followed by a number ranging from 1 to 2500.
Am looking for a formula to display just the number without the dots
in a column on another worksheet
For example:
..........64 would display as 64
...456 would display as 456
......................22 would display as 22

Looked at SUBSTITUTE, LEN, TRIM, and all I'm getting for results is a
partial or a headache.

Thanks for any ideas.

Rick

 

[Glenn]

Have a column of data containing from the left any number of dots
followed by a number.
The columns can contain anywhere from none, to up to 25 dots, and
followed by a number ranging from 1 to 2500.
Am looking for a formula to display just the number without the dots
in a column on another worksheet
For example:
.........64 would display as 64
..456 would display as 456
.....................22 would display as 22

Looked at SUBSTITUTE, LEN, TRIM, and all I'm getting for results is a
partial or a headache.

Thanks for any ideas.

Rick

SUBSTITUTE should work.

=SUBSTITUTE(A1,".","")

If not, describe your results.

 

[John C]

Your problem is the ellipses. The default for MS Office products is to change
3 consecutive periods with an ellipses(sp?). Thereby the ... becomes 1 single
character.

You need a nested substitution.

=SUBSTITUTE(SUBSTITUTE(A1,"…",""),".","")

Hope this helps.

 

[Rick]

SUBSTITUTE should work.

=SUBSTITUTE(A1,".","")

If not, describe your results.- Hide quoted text -

- Show quoted text -

Glenn, thank you. Works fine. I think I was missing a widget in the
formula.
Rick

 

[Rick]

Your problem is the ellipses. The default for MS Office products is to change
3 consecutive periods with an ellipses(sp?). Thereby the ... becomes 1 single
character.

You need a nested substitution.

=SUBSTITUTE(SUBSTITUTE(A1,"…",""),".","")

Hope this helps.
--
John C








- Show quoted text -

John, that worked fine as well.
Thanks for taking a look at it.
Rick