Looking to replace "dots"

  • Thread starter Thread starter Rick
  • Start date Start date
R

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
 
Rick said:
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
Click to expand...


SUBSTITUTE should work.

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

If not, describe your results.
 
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.
 
SUBSTITUTE should work.

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

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

- Show quoted text -
Click to expand...

Glenn, thank you. Works fine. I think I was missing a widget in the
formula.
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 -
Click to expand...

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