Excel cell search for the first number and copy all the words before that.

advertisements

I have a list of entries that is a mix of words, letters, and numbers. I want to only copy the words that happen before the first number.

Term Loan
Subordinated, 10% due 6/23/2020
Subordinated Debt (Interest Rate 12.00%, Maturity Date Due 2/15/19)
One stop (10.01%, Due 12/1/18)
Second Lien Term Loan to CP Well Testing, LLC (9.00% (LIBOR + 7.00% with 2.00% LIBOR floor) plus 9.00% PIK, due 4/1/2019)
(Prime Plus 2%, Current Coupon 5.25%, Secured Debt (Maturity - November 14, 2013))

The formula I'm trying to think of would return:

Term Loan
Subordinated
Subordinated Debt
One Stop
Seccond Lien Term Loan to CP Well Testing, LLC
(Secured Debt)

I know that if I searched for the first number, the results would include some unwanted parentheses, and maybe some extra words--especially in that last case--but this was the best I could think of to solve my problem.

Thanks in advance


How about this? If your string is in cell A1, =LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

I borrowed the search formula from ExtendOffice, below. It hard codes an array to search for with the curly brackets (type them, don't hit control enter), and uses the concatenated numbers so that none of the searches return an error. If you don't have any numbers, it returns the position of the concatenated 0, and therefore you get your whole string.

http://www.extendoffice.com/documents/excel/2510-excel-find-first-last-number-in-string.html