Search code examples
excelexcel-formulaexcel-2013

Split strings into their alphabetic and numeric components

I am using Excel 2013. I want to trim 'words' in Excel that end with numbers, eg: 

david22
yuvi1
michell555

to result in:

david   22
yuvi    1
michell 555

Can anyone please help?

Solution

  • You should try this if you don't want to use vba:

    I am assumming your data is in A column, so below formula should be kept at C column, Drag this formula untill your data is there in Column A.

    =+SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

    THEN

    use the below formula in column B and drag it to last.

    =LEFT(A1,SEARCH(C1,A1)-1)

    Hope this helps

    Attached is screenshot for your reference.

    enter image description here

    Some Caveat:

    1) - The input string in column A must be shorter than 25 characters

    2) - There must be at most 14 digits in the input string. (Any following digits will be shown as zeroes.)

    But for the question you asked it should work perfectly fine.