How to find first non numeric character in the string using Excel?

excel
data_wrangling
string

#1

Hi,

I have alphanumeric values stored in a columns like:

2345YUI89
567UI90
1234679*UI08

Now, I want to find the position of first non numeric character in the string. Please help me to find this.

Thanks, Mukesh


#2

Which tool are you using?


#3

Hi,

I want to perform this using excel.

Thanks, Mukesh


#4

@Mukesh

You can perform this using excel array formula or VBA coding. I am solving it with excel formula only.

=MATCH(1,ISERROR(VALUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))*1,0)

Press CTRL+SHIFT+ENTER after entering formula to cell.

Explanation:

  • ROW(INDIRECT(“1:” & LEN(A1))) returns you array {1,2,3,…,Len(A1)}
  • Function MID used to take each character in A1 cell: MID(A1,ROW(INDIRECT(“1:” & LEN(A1))),1)
  • Function VALUE() used to convert each character to number. If it is character then it will return #VALUE! else covert numerical value to number
  • Function ISERROR() used to check, it has error value or not and after that it is multiplied with 1 to convert TRUE to 1 and FALSE to 0.
  • Finally used function Match to return the first position of 1 (a character value).

Regards,
Sunil