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

#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