Hi sir and Excel expertise I like to ask you one thing I am having data with alphanumeric product data so I want to extract only alphabets form that alphanumeric
Example
Ab25cdf=abcdf
Gh78v10y=ghvy
Zxvu547tf=zxvutf
Please help
Hi sir and Excel expertise I like to ask you one thing I am having data with alphanumeric product data so I want to extract only alphabets form that alphanumeric
Example
Ab25cdf=abcdf
Gh78v10y=ghvy
Zxvu547tf=zxvutf
Please help
Try this:
=LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&{1,2,3,4,5,6,7,8,9,0}))-1)
Click the * to say thanks.
that cut to first number only ...
Hi,
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")
Regards
that cut to first number only ...
put into related cell ={TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),""))} array so CSE needed
I've spot now that you are using excel2010 so TEXTJOIN can't be used....
Anyway maybe somebody else use this if needed.
try also ={CONCAT(IF(ISERR(MID(E4,ROW(INDIRECT("1:100")),1)+0),MID(E4,ROW(INDIRECT("1:100")),1),""))}
Last edited by KOKOSEK; 01-18-2019 at 10:26 AM. Reason: CONCAT option added
Maybe... anyway as I said maybe both method will be used by someone else.
HOWEVER yours is much quicker (no array formula).
beepetark are you open to a VBA solution?
This User Defined Function by Rick Rothstein makes short work of this.
If you are not familiar with how to install VBA code copy this code, go to the VBA editor (Alt F11), insert new Module and paste this into that module.
Then use this formula ... source data starting in A1PHP Code:
Function NoDigits(ByVal s As Variant) As Variant 'by Rick Rothstein
Dim i As Long
For i = 1 To Len(s)
If Mid(s, i, 1) Like "#" Then Mid(s, i, 1) = Chr(1)
Next i
NoDigits = Replace(s, Chr(1), "")
End Function
You will now need to save the file in a macro enabled format.Formula:Please Login or Register to view this content.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks