Using a formual to trim a string

[jeffmoseler]

I have a column of data that looks like this:

Calcium fluoride. (CaF2) made/coated crucibles ............................................................... 2A225.a.1

How could I write a formula that would take everything to the right of the "......."s?

 

[Rick Rothstein]

I have a column of data that looks like this:

Calcium fluoride. (CaF2) made/coated crucibles ............................................................... 2A225.a.1

How could I write a formula that would take everything to the right of the "......."s?

If there is always a space after all those dots, then you can use this...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))

otherwise use this...

=TRIM(MID(TRIM(RIGHT(SUBSTITUTE(A1,"....",REPT(" ",300)),300)),4,99))

 

[vboure]

=right(a1;len(a1)-find(".. ";a1)-2)

As long as there will never be another occurence of ".. " (that's two dots followed by a space) in your string.

 

[jeffmoseler]

OK, it didn't work and I'm not sure why. I'll include more of a sample so you can see where it did work and where it didn't.
The first formula you wrote returned a blank cell and the second worked sometimes but started midway into the string on others.

Ablative liners, thrust or combustion chambers ............................................. 9A106.a, 9A619.c, .e Returned: A106.a, 9A619.c, .e Need to Return: 9A106.a, 9A619.c, .e
Abrin ................................................................................................................................. 1C351.d.1 Returned: C351.d.1 Need to Return: 1C351.d.1
Absolute reflectance measurement equipment .................................................................... 6B004.a Returned: 04.a Need to Return: 6B004.a
Absorbers of electromagnetic waves ...................................................................................... 1C001 Returned: 1C001 Need to Return: 1C001
Absorbers, hair type ............................................................................................... 1C001.a Note 1.a Returned: 1C001.a Note 1.a Need to Return: 1C001.a Note 1.a

There is not always spaces between the "..." and the suffix.

 

[Rick Rothstein]

OK, it didn't work and I'm not sure why. I'll include more of a sample so you can see where it did work and where it didn't.
The first formula you wrote returned a blank cell and the second worked sometimes but started midway into the string on others.

Ablative liners, thrust or combustion chambers ............................................. 9A106.a, 9A619.c, .e Returned: A106.a, 9A619.c, .e Need to Return: 9A106.a, 9A619.c, .e
Abrin ................................................................................................................................. 1C351.d.1 Returned: C351.d.1 Need to Return: 1C351.d.1
Absolute reflectance measurement equipment .................................................................... 6B004.a Returned: 04.a Need to Return: 6B004.a
Absorbers of electromagnetic waves ...................................................................................... 1C001 Returned: 1C001 Need to Return: 1C001
Absorbers, hair type ............................................................................................... 1C001.a Note 1.a Returned: 1C001.a Note 1.a Need to Return: 1C001.a Note 1.a

There is not always spaces between the "..." and the suffix.

How about a UDF instead of a formula then?

Function AfterDots(S As String) As String
  Dim Dots() As String
  Dots = Split(S, "...")
  AfterDots = Trim(Replace(Replace(LTrim(Replace(Replace(Dots(UBound(Dots)), _
                   " ", Chr$(1)), ".", " ")), " ", "."), Chr(1), " "))
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 AfterDots just like it was a built-in Excel function. For example,

=AfterDots(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.

 

[jeffmoseler]

The UDF worked perfectly! Very handy thank you so much. I even reversed it to get the data before the dots.

Function BeforeDots(S As String) As String
  Dim Dots() As String
  Dots = Split(S, "...")
  BeforeDots= Trim(Replace(Replace(LTrim(Replace(Replace(Dots(LBound(Dots)), _
                   " ", Chr$(1)), ".", " ")), " ", "."), Chr(1), " "))
End Function

 

[Rick Rothstein]

The UDF worked perfectly! Very handy thank you so much. I even reversed it to get the data before the dots.

Function BeforeDots(S As String) As String
  Dim Dots() As String
  Dots = Split(S, "...")
  BeforeDots= Trim(Replace(Replace(LTrim(Replace(Replace(Dots(LBound(Dots)), _
                   " ", Chr$(1)), ".", " ")), " ", "."), Chr(1), " "))
End Function

You are welcome. Finding the text after the multiple dots turned out to be a harder problem to find a formula for because of the variability in the number of dots, the fact that a space could be located in the "middle" of them and that single dots could be located after them, hence the UDF solution (VBA can be constructed easier than formulas in some cases). However, to find the text before those multiple dots is an easier task and there is a fairly simple formula solution for that problem...

=TRIM(LEFT(A1,FIND("..",A1&"..")-1))