# Split a string after last alphabetic character

1. ## 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...

2. ## 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. ## 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

4. ## 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

5. ## 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. ## 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. ## Re: Split a string after last alphabetic character

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

8. ## 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. ## Re: Split a string after last alphabetic character

zbor... cheers, a mystery solved (I think!!).

10. ## 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. ## 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. ## 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. ## Re: Split a string after last alphabetic character

Originally Posted by daddylonglegs
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. ## Re: Split a string after last alphabetic character

Originally Posted by zbor
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. ## 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

16. ## Re: Split a string after last alphabetic character

Thanks DLL, I followed your excellent explanation.

17. ## Re: Split a string after last alphabetic character

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

18. ## Re: Split a string after last alphabetic character

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

where test is
Formula:
`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 )

##### Users Browsing this Thread

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

#### 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