Using a formual to trim a string

jeffmoseler

Well-known Member
Joined
Jul 16, 2004
Messages
539
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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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))
 
Last edited:

vboure

New Member
Joined
Aug 5, 2010
Messages
3
Code:
=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

Well-known Member
Joined
Jul 16, 2004
Messages
539
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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?

Code:
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

Well-known Member
Joined
Jul 16, 2004
Messages
539
The UDF worked perfectly! Very handy thank you so much. I even reversed it to get the data before the dots.

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
The UDF worked perfectly! Very handy thank you so much. I even reversed it to get the data before the dots.

Code:
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top