Macro to trim a cell and remove string "THRU"

[Rocker7]

I am trying to build 2 macros to trim the contents of all cells in a column. The cells contain the text: #### THRU #### (the numbers could be more or less than 4 digits).

For column A, I need a macro that will take the contents of each cell in the column and using the 1st space as a delimiter delete the space to the end of the cell.

Example: 1111 THRU 9999
The macro will result in just 1111 remaining in the cell. The string "THRU 9999" would be deleted.


For colunn B, I need a macro that will use the 1st position in the cell as a delimiter and delete everything up to and including the 2nd space, but leave the 2nd group of numbers alone.

Example: 1111 THRU 9999
The macro will result in the string "1111 THRU " being deleted and just leave 9999 in the cell.

Any help would be greatly appreciated. Thanks.

[JBeaucaire]

You can do this with formulas:

For the first string:
=LEFT(A1, FIND(" ", A1)-1)

For the second string:
=LEFT(A1, FIND("U ", A1))

You can copy those formulas down a couple of columns and instantly have your strings.

[Rocker7]

Thanks Jerry.

The first formula works fine, but the second formula doesn't return what I am looking for.

If I have V203X THRU V2032 the second formula returns "V203X THRU".

I need the second formula to return "V2032".

I played around and tried using the following formula:
=RIGHT(B1, FIND("RU",B1)-4)

However, this formula doesn't work in all my cells because many of the cells have a varying number of trailing spaces. Those extra spaces break my formula.

Any suggestions on tweaking the second formula to leave behind just the second number?

[JBeaucaire]

Like so:
=RIGHT(A1, LEN(A1)-FIND("U ", A1)-1)

Notice the "space" after the U in the string?

[Rocker7]

Your a genius!

That worked thanks!

I had one small problem that I fixed. If a cell did not contain the string "THRU" a error message "#value" would appear in the cell.

I added an "ISERROR" check to the formlua so that if string
"THRU" was not found then the formula would return a blank cell instead of
"#value".

Here is the final version of the formula and an explanation of how ISERROR works in case it helps anyone out with a similar problem.

=IF(ISERROR(FIND("THRU",C2)),"",RIGHT(C2, LEN(C2)-FIND("U ",C2)-1))

How ISERROR works:

IF(ISERROR(what error to check for),ErrorExists,ErrorDoesNotExist)

ISERROR starts out by specifying what to check for. In the formula its looking to see if string "THRU" can be found in cell C2.

In the formula where you see the two double quotes together, that is the action the formula will take if an error does exist meaning that string "THRU" cannot be found. There is nothing between the double quotes in my example so the cell will be left blank if string "THRU" cannot be found. I could for example instead of using the two double quotes next to each other use "NOT FOUND" and then the string "NOT FOUND" would populate the cell if string "THRU" can't be found.
Example:
=IF(ISERROR(FIND("THRU",C2)),"NOT FOUND",RIGHT(C2, LEN(C2)-FIND("U ",C2)-1)) <--Note: When working with strings (text) in a formula you must enclose the string in between quotes.

If an error does not exist, meaning string "THRU" could be found in C2, then the formula will perform the action in the ErrorDoesNotExist portion of the formula which in this example is: RIGHT(C2, LEN(C2)-FIND("U ",C2)-1 which in my case will return the value after the string "THRU".

Thanks again. Made my Friday a lot easier.

[JBeaucaire]

Thanks for the tutorial on ISERROR(). That's one of many useful doublecheck tools. ISNA(), ISNUMBER(), ISTEXT(), ISBLANK() are a few more you might want to keep in mind.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.