Delete leading and trailing periods

**evildave** · 06-04-2009, 08:59 AM

I have a spreadsheet with numbers of various lengths.

Sometimes they are preceded with a period and have one at the end as well.

Examples: of their present state

.11110000.111111.000000.

111111.1111111.000000.

100000.000000.0000.110001.

Examples: of desired state

10000.111111.000000

111111.1111111.000000

100000.000000.0000.110001

Is their a way I can create a new column
and insert a formula, then drag it down so that it will delete the first or last character, if the character is a period?

Thanks in advance

Dave

**NBVC** · 06-04-2009, 09:10 AM

Try:

Please Login or Register  to view this content.

copied down.. where A1 contains first number to parse

**evildave** · 06-04-2009, 09:37 AM

NBVC:

Thanks for your reply it works but leaves carriage returns on the trailing
number if no period is present.

I can scrub it with a clean function after using your formula.

Thanks again,

Dave

**NBVC** · 06-04-2009, 09:39 AM

Try then:

Please Login or Register  to view this content.

**evildave** · 06-04-2009, 10:47 AM

Many thanks,

that works quite well.

Regards,

Dave

**NBVC** · 06-04-2009, 10:52 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Teethless mama** · 06-04-2009, 11:02 AM

Originally Posted by evildave

I have a spreadsheet with numbers of various lengths.

Sometimes they are preceded with a period and have one at the end as well.

Examples: of their present state

.11110000.111111.000000.

111111.1111111.000000.

100000.000000.0000.110001.

Examples: of desired state

10000.111111.000000

111111.1111111.000000

100000.000000.0000.110001

Is their a way I can create a new column
and insert a formula, then drag it down so that it will delete the first or last character, if the character is a period?

Thanks in advance

Dave

Try this:

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