0

I have few strings in different cells in an excel file. In each cell the strings are the combination of letters and numbers. I wish to separate the portion of text using excel formula until it reaches any number in a cell, as in Range("A1") contains I spent 30 dollars for nothing and I wish to get the portion upto the number 30 which is I spent. To be more specific, I wish to get the portion like ColumnB did (first portion separated by a number) using any excel formula.

ColumnA                                   ColumnB
I spent 30 dollars for nothing            I spent
His ID has changed from 2341 to 2435      His ID has changed from
SIM
  • 21,997
  • 5
  • 37
  • 109
  • Have a look at : https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835 – Solar Mike Jan 21 '18 at 23:23

2 Answers2

1

try this as an array formula with CSE.

=TRIM(LEFT(A2, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A2)-1, LEN(A2)))))

enter image description here

  • What is the purpose of `MIN` here? The `LEN(A2)` is the second argument of the `IFERROR`. `MIN` has only one argument, making it superfluous? – ImaginaryHuman072889 Jan 22 '18 at 01:19
  • No, min has 10 arguments; specifically the positions for each digit from 0-9. min returns the first digit found. use evaluate formula for a better understanding. –  Jan 22 '18 at 01:26
1

Assuming Your data is in A2 cell,

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1))

This formula will extract the text before any numeric value as you have mentioned in the question.

Vipul Karkar
  • 327
  • 1
  • 3
  • 11