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

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

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

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

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

@ 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,

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.

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

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

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.

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

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

Thanks DLL, I followed your excellent explanation.