Hi,
I have a sheet that contains long ranges of serialnumbers. In some cases,
the serial number is formatted incorrectly and then contains a period "."
The period can be found in any position in the cell, but typically appears
as the first or the last character.
I am looking for a macro to remove the period from the cell. I have found a
helpful formula on this forum but I cannot / don't know how to include that
into a macro.

The formula is
=IF(LEFT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",IF(R IGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",MID(B2,2 ,LEN(B2)-2),MID(B2,2,LEN(B2)-1)),IF(RIGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".", LEFT(B2,LEN(B2)-1),B2))

thanks

Sure, I can help you with that. Here's a step-by-step guide on how to create a macro to remove periods from cells in Excel:

1. Open your Excel sheet and press Alt + F11 to open the Visual Basic Editor.
2. In the editor, click on Insert Module to create a new module.
3. In the module, paste the following code:
   
   
   Code:

   Sub RemovePeriods()
       Dim cell As Range
       For Each cell In Selection
           cell.Value = Replace(cell.Value, ".", "")
       Next cell
   End Sub

4. Save the module and close the editor.
5. Go back to your Excel sheet and select the cells that you want to remove periods from.
6. Press Alt + F8 to open the Macro dialog box.
7. Select the RemovePeriods macro and click Run.
8. The macro will remove all periods from the selected cells.

That's it! You can now use this macro anytime you need to remove periods from cells in Excel.

if you simply want to remove all the periods (Full Stops) from a string use:-

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

Mike

"Eric S." wrote:

Hi,
I have a sheet that contains long ranges of serialnumbers. In some cases,
the serial number is formatted incorrectly and then contains a period "."
The period can be found in any position in the cell, but typically appears
as the first or the last character.
I am looking for a macro to remove the period from the cell. I have found a
helpful formula on this forum but I cannot / don't know how to include that
into a macro.

The formula is
=IF(LEFT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",IF(R IGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",MID(B2,2 ,LEN(B2)-2),MID(B2,2,LEN(B2)-1)),IF(RIGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".", LEFT(B2,LEN(B2)-1),B2))

thanks

Hi Eric,

EditReplace
Find what: .
Replace with: (leave this blank)
Click Replace All

That should do what you ask although you do say in some cases
so you may have to select your target cells first in
order to contain the find and replace to where you want.

HTH
Martin


"Eric S." wrote in message
...
Hi,
I have a sheet that contains long ranges of serialnumbers. In some cases,
the serial number is formatted incorrectly and then contains a period "."
The period can be found in any position in the cell, but typically appears
as the first or the last character.
I am looking for a macro to remove the period from the cell. I have found
a
helpful formula on this forum but I cannot / don't know how to include
that
into a macro.

The formula is
=IF(LEFT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",IF(R IGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",MID(B2,2 ,LEN(B2)-2),MID(B2,2,LEN(B2)-1)),IF(RIGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".", LEFT(B2,LEN(B2)-1),B2))

thanks

Maybe I should eplain that a bit better.

If only one cell is selected then the Find and Replace
will be applied globally to the worksheet..

If more one cell is selected then the Find and Replace
will be applied only to the selected cells.

HTH
Martin


"MartinW" wrote in message
...
Hi Eric,

EditReplace
Find what: .
Replace with: (leave this blank)
Click Replace All

That should do what you ask although you do say in some cases
so you may have to select your target cells first in
order to contain the find and replace to where you want.

HTH
Martin


"Eric S." wrote in message
...
Hi,
I have a sheet that contains long ranges of serialnumbers. In some cases,
the serial number is formatted incorrectly and then contains a period "."
The period can be found in any position in the cell, but typically
appears
as the first or the last character.
I am looking for a macro to remove the period from the cell. I have found
a
helpful formula on this forum but I cannot / don't know how to include
that
into a macro.

The formula is
=IF(LEFT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",IF(R IGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".",MID(B2,2 ,LEN(B2)-2),MID(B2,2,LEN(B2)-1)),IF(RIGHT(CONCATENATE(LEFT(B2),RIGHT(B2)))=".", LEFT(B2,LEN(B2)-1),B2))

thanks