+ Reply to Thread
Results 1 to 18 of 18

Split a string after last alphabetic character

  1. #1
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,977

    Split a string after last alphabetic character

    A short time ago, this post

    http://www.excelforum.com/excel-gene...-one-cell.html

    received two very nice replies (Pike & Alkey). I thought that I'd play with these for a while, to try and make them more generic.

    If I wanted to separate a string after the last alphabetical character (eg Fred999 would become Fred and 999 in separate cells), the solutions offered, might provide a neat way of doing so.

    However, Alkey's formula fell over with Fred999[66] and similar, 'cos of the CHAR range selected. I changed that. But them, for reasons totally beyond my understanding, all 3 formulas fall over with certain strings.

    Why? What needs to be done to fix it?

    Dazed & confused...
    Attached Files Attached Files
    Glenn



  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Split a string after last alphabetic character

    They fail if you have duplicated last letter.

    Like:
    Fred999 is OK
    Frrrrrrred999 is OK
    Fdred999 would fail (Fdre + d999)
    Fedred999 would fail leavin' r as last unique character (Fedr + ed999)

    That'y just analysis. Not a solution.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split a string after last alphabetic character

    There are different formulas for different things

    try this array formula

    =MID(LEFT(A1,MATCH(1,-MID(A1,ROW(A$1:A$99),1),1)),MIN(SEARCH(ROW(A$1:A$10)-1,A1&1234567890)),99)

    Row\Col
    A
    B
    1
    HaPpY456 456
    2
    Happy789.98[11] 789.98[11
    3
    Happy(999) 999
    4
    HappY.908 908
    5
    fitzsimons123 123
    6
    Needlesstosay124 124
    7
    needlesstosays124 124
    8
    Fred999 999
    9
    All the young dudes124 124
    10
    Need less to says124 124
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Split a string after last alphabetic character

    Assuming by "alphabetic character" you simply mean any letter A to Z (upper or lower case) then you can use this [non-array] formula in B1 to get everything up to and including that last character:

    =LEFT(A1,MATCH(2,INDEX(1/(ABS(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-77.5)<13),0)))

    and then get the rest of the string in C1 with this formula

    =SUBSTITUTE(A1,B1,"",1)

    That will split HappY(999) into HappY in B1 and (999) in C1, for example

    If there are no letters in A1 you'll get errors
    Audere est facere

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,977

    Re: Split a string after last alphabetic character

    Thanks for this. Daddylonglegs' solution is better (it always returns the text cleanly).

    Your hekpful advice is appreciated! ...I'm here to learn.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split a string after last alphabetic character

    @ DLL

    It is a very nice formula and works great too I do have couple questions to ask if you don't mind. Why -77.5? And why ROW(INDIRECT("1:"&LEN(A1))) and just something like ROW(A$1:A$255),1))). I tried it like this =LEFT(A2,MATCH(2,INDEX(1/(ABS(CODE(UPPER(MID(A2,ROW(A$1:A$255),1)))-77.5)<13),0))) and it works just fine.

    Thank you,

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Split a string after last alphabetic character

    77.5 is middle between 65 (A) and 90 (Z).

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,977

    Re: Split a string after last alphabetic character

    I guess the row-indirect bit is just a speed issue, no point in making the array larger than it needs to be. As for the 77.5... I'm still trying to figure it out. I have seen it used somewhere before, but.... just where i can't recall.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,977

    Re: Split a string after last alphabetic character

    zbor... cheers, a mystery solved (I think!!).
    Last edited by Glenn Kennedy; 12-29-2014 at 12:39 PM.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Split a string after last alphabetic character

    That way you can split ranges as 77.5-90(Z) and 77.5-65(A) into desired (below 0) and non-desired (above zero).

    Something like on this graph (don't look numbers, just shape) http://www.shelovesmath.com/wp-conte...sformation.png

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Split a string after last alphabetic character

    Hello Alkey,

    Of course ROW(INDIRECT("1:"&LEN(A1))) will work with any number of characters in the cell (while ROW(A$1:A$255) will only work for up to 255 characters), but I prefer using INDIRECT because it ensures that the correct array of numbers is always returned. If you use just ROW(A$1:A$255) then you need to be careful if you add rows in the worksheet, e.g. add 10 rows at the top of the worksheet and that will become ROW(A$11:A$265).....and potentially mess up the results. INDIRECT will always return the same array in those circumstances.

    77.5 is the midpoint of the range 65 - 90 (the code numbers of the capital letters) so if you subtract 77.5 form the character code and take the absolute value then only the letters are < 13, anything > 13 is another type of character. Doing it that way just means you don't need two checks (> 65 and < 90) which makes the formula a little longer given the number of functions involved in each check

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Split a string after last alphabetic character

    Or imagine you need find values between, I don't know, 30 and 33.
    You can either find all that are greater than 30 AND lower than 33, or you can find all that are around 31.5 +-1.5

  13. #13
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split a string after last alphabetic character

    Quote Originally Posted by daddylonglegs View Post
    Hello Alkey,

    Of course ROW(INDIRECT("1:"&LEN(A1))) will work with any number of characters in the cell (while ROW(A$1:A$255) will only work for up to 255 characters), but I prefer using INDIRECT because it ensures that the correct array of numbers is always returned. If you use just ROW(A$1:A$255) then you need to be careful if you add rows in the worksheet, e.g. add 10 rows at the top of the worksheet and that will become ROW(A$11:A$265).....and potentially mess up the results. INDIRECT will always return the same array in those circumstances.

    77.5 is the midpoint of the range 65 - 90 (the code numbers of the capital letters) so if you subtract 77.5 form the character code and take the absolute value then only the letters are < 13, anything > 13 is another type of character. Doing it that way just means you don't need two checks (> 65 and < 90) which makes the formula a little longer given the number of functions involved in each check
    This was an excellent explanation. I really appreciate your time and effort.

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split a string after last alphabetic character

    Quote Originally Posted by zbor View Post
    Or imagine you need find values between, I don't know, 30 and 33.
    You can either find all that are greater than 30 AND lower than 33, or you can find all that are around 31.5 +-1.5
    Thank you zbor very much

  15. #15
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Split a string after last alphabetic character

    mind you if you have a sheet you are NEVER going to insert rows in
    you could use
    =ROW(Sheet3!$A$1:$A$255) or
    =ROW(Sheet3!1:255)
    but that would probably too problematic to use
    Last edited by martindwilson; 12-29-2014 at 01:07 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,977

    Re: Split a string after last alphabetic character

    Thanks DLL, I followed your excellent explanation.

  17. #17
    Registered User
    Join Date
    04-12-2013
    Location
    Cicero, NY
    MS-Off Ver
    Excel 2013
    Posts
    35

    Re: Split a string after last alphabetic character

    In excel 2013, wouldn't "Flash Fill" work perfectly for this?

  18. #18
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Split a string after last alphabetic character

    Or you can set dynamic range named i.e. test

    where test is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    so you need to take care only on A1.

    And this also can be on separate sheet as martindwilson pointed (locked and very hidden )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Split string based on character count
    By cedric_dranreb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2013, 05:14 AM
  2. Replies: 2
    Last Post: 06-22-2013, 01:41 PM
  3. Count number of columns and return an Alphabetic character?!
    By Wino in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2009, 07:59 AM
  4. find and select all cells with a alphabetic character
    By Giz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2005, 02:05 PM
  5. [SOLVED] Add a alphabetic character to the beginning of an entire column of
    By Karise in forum Excel General
    Replies: 1
    Last Post: 08-18-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1