Looking to replace "dots"
 

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

Looking to replace "dots"
 

Rick wrote:
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.

Looking to replace "dots"
 

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


"Rick" wrote:

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

Looking to replace "dots"
 

On Jul 15, 9:23*am, Glenn wrote:
Rick wrote:
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.- Hide quoted text -

- Show quoted text -

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

Looking to replace "dots"
 

On Jul 15, 9:36*am, John C <johnc@stateofdenial wrote:
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



"Rick" wrote:
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- Hide quoted text -

- Show quoted text -

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