Insert space after non-numeric character and a period

[descher]

Is there a way to check for a space between a non-number character, a period, and the next non-number character? In other words, I want to check text cells to make sure I don't have something like, "Attain the ridge line at 0.25 miles.Once there, head north."

I want Excel to find "miles.Once" but not "0.25." Then I want to insert a space so that it become "miles. Once"

It seems like I need to add a condition to a statement like this:
=TRIM(SUBSTITUTE(A2,".",". "))

Thank you in advance for your help.
Daniel

 

[Rick Rothstein]

I am not sure there is a formula solution that only uses Excel functions possible for that; however, a UDF (user defined function) can be created to do it...

Function Periods(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, ".")
  For X = 1 To UBound(Parts)
    If Left(Parts(X), 1) Like "[!0-9 ]" Then Parts(X) = " " & Parts(X)
  Next
  Periods = Join(Parts, ".")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Periods just like it was a built-in Excel function. For example,

=Periods(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[descher]

Rick, thanks for your quick response! Unfortunately, I'm using Excel 2008 for Mac, so no VB capabilities to test it now. I'll try later on Windows.

Cheers.

I am not sure there is a formula solution that only uses Excel functions possible for that; however, a UDF (user defined function) can be created to do it...

Function Periods(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, ".")
  For X = 1 To UBound(Parts)
    If Left(Parts(X), 1) Like "[!0-9 ]" Then Parts(X) = " " & Parts(X)
  Next
  Periods = Join(Parts, ".")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Periods just like it was a built-in Excel function. For example,

=Periods(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[István Hirsch]

Are you expecting many mistakes of that kind? If yes, pre-spellcheck automatic correcting may be very useful and time-saving.
If no, finding and correcting these mistakes may be considered to be delayed until spell checking (Review/Spelling), which you will probably perform in any case if longer texts are involved (of course, then only one by one). In addition, spell checkers can handle special cases, e.g. „gmail@pet.com” , „a.m.”, „e.g” etc. or at least stops at the problematic words.