## Excel formula help please

## Excel formula help please

(OP)

Cell A2 will have numbers with one or two letters mixed in. The letters are always together.

3L481

27H429

9SB75

etc...

In A1 I would like to place a formula that will return the numbers in the beginning along with the letters, but not the numbers after the letters.

3L

27H

9SB

I never know for sure what letters or numbers will be used.

I cant seem to find any examples of this.

3L481

27H429

9SB75

etc...

In A1 I would like to place a formula that will return the numbers in the beginning along with the letters, but not the numbers after the letters.

3L

27H

9SB

I never know for sure what letters or numbers will be used.

I cant seem to find any examples of this.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Excel formula help please

## RE: Excel formula help please

## RE: Excel formula help please

Im surprised there isnt a formula that will simply extract text from a cell and not numbers. So it would take a cell that has 7BA358 and return BA. Then it would be simple to find the location of the BA and use a LEFT formula.

Currently, I scanned through all of the cells and found there were three different letters being used in this particular file and did the following formula...

=LEFT(F780,IFERROR(FIND("H",F780,1),IFERROR(FIND("K",F780,1),IFERROR(FIND("L",F780,1),"."))))

Which is basically what phamENG was saying. The problem is not all files I work with will have those same letters and some will have more than 3. It would be nice to have a specific formula I could enter and find the first numbers and letters no matter what numbers or letters they use.

3DDave, I might have to look into yours further. Maybe I can find a way to combine them all into one formula. Maybe not but it might be worth looking into.

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Excel formula help please

## RE: Excel formula help please

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Excel formula help please

## RE: Excel formula help please

## CODE

## RE: Excel formula help please

Here you go.

Big formula, but all in one cell.

Not even Google knows how to do this,

https://www.dropbox.com/s/uvhin7zcnfbzf9w/GET%20LE...

## RE: Excel formula help please

Denial and 1503-44 - nicely done.

## RE: Excel formula help please

## RE: Excel formula help please

I was going to have a go at the VBA, but as Denial beat me to it, I will just say:

- The "Dim Result as string" is not used so can be deleted (picky, I know)

- I was going to correct it to work with lowercase letters, but I see the comparisons in VBA (and on the spreadsheet) are not case sensitive, so it works with with lowercase as it is.

- For the non VBA lovers out there, to use Denial's code:

Open a new spreadsheet (or an existing one you want to use the function in) and save with a chosen name.

Press Alt-F11 to open the VBA editor.

Right-click on VBAProject(spreadsheet name) in the list of open files on the left, and Insert-Module

Copy Denial's code and paste it in the new module.

That's all.

You can then use the Kenja function, just like any built-in Excel function.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

Dim i As Integer

For i = Len(X) To 1 Step -1

If (InStr("0123456789", Mid(X, i, 1))) = 0 Then

get_left = Left(X, i)

Exit Function

End If

Next i

End Function

This is more flexible as the characters of interest are just put into a list.

It would be an alternative to make the list of characters an argument for the function.

Integer should be fine as Integer handles up to 2,147,483,647 which is the maximum length for a VBA string.

This does not need the test for length as null strings return null strings.

## RE: Excel formula help please

Not very important, but Longs are actually faster than Integers, and have three less characters to type, so I always use Longs, which saves thinking about it.

More importantly the Integer limits are -32768 to +32767. 2,147,483,647 is the limit for a Long.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

Spolsky - this is your fault. /s

Edited - nice try but it takes 3 keystrokes to type Integer and 4 to type Long.

## RE: Excel formula help please

The Microsoft documentation (after much searching) confirmed your number was right.

It's still easier to always use Longs and not worry about it though :)

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

Edited also: Using the shortcuts, they both take 3.

## RE: Excel formula help please

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

I didn't just look at the first one; it was the first bunch of them. VBA Integer - Now I wonder if it's just overzealousness of Google to PageRank the most linked to pages or a subtle dig at MS; what am I saying, MS has done enough double-speak badly documented on-line material.

I'm more worried/impressed that you know Longs are faster. It's likely my age and too many languages. Every time I work on VBA it takes a while to get the different function names than used on the worksheets and from C, et al. I so want to use debug.print sizeof(Integer); in VBA.

This works:

Function junk() As Integer

junk = 2 ^ 15 - 1

End Function

This fails.

Function junk() As Integer

junk = 2 ^ 15

End Function

I tried dropbox - it told me to log in. But what I see is that (I use NoScript) only 2 of three scripts were presented for approval the first page load. The third one must do some other thing and be loaded by one of the first two. Without that it sends me to the login page. I see that I go to the site, allow two scripts; it reloads; then I have to go back and allow a third one. Sigh. (The script apparently saves a cookie.)

Anyway, now downloaded through the phalanx of scripts, looking at the formula - Holy Cow. I guess it's for when regex is too simple.

When I use the shortcut it requires LON<tab> vs IN<tab>; perhaps another MS upgrade? I'm at Office 2010.

## RE: Excel formula help please

Related to 24*60*30 in VBA raising an error:

"In short, the problem is that the result data type defaults to the largest type of the values being operated on, so if they are all integers the result is an integer, and since the largest value for an integer is 32767 you get an overflow."

Full article: https://newtonexcelbach.com/2019/10/01/a-problem-w...

I just checked nothing had changed:

## CODE -->

returns an error, but

## CODE -->

returns 43200

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

This part of that formula:

=SUM(LEN("3L481") - LEN(SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},""))) shows the answer "4"

It uses the SUM() but has only one argument, or so it appears. (I've replaced the cell reference of interest with example text to follow along better.)

Then I look at the contents: =LEN("3L481") shows the answer (expected) "5"

Then I look at the next item =LEN(SUBSTITUTE("3L481",{"0","1","2","3","4","5","6","7","8","9"},"")) which also answers "5" though I expected the answer to be 1 as the substitutions should reduce all the digits to nulls and leave only the one "L" for a length of one.

edit: Nope - of course, =SUBSTITUTE("ABC",{"A","B"},"") returns "BC"

So the SUM() of 5 - 5 = 4.

However, on it's own =SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},"") produces "3L481" which is correctly reported by LEN().

Finally =SUM(LEN(SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},""))) produces the answer "46".

All in Office 2010, so if this all works differently in some new version I would love to see the bug reports or I'll need a brain scan for a major bleed or blockage.

## RE: Excel formula help please

At first I also wanted to do this with a UDF, but it seemed Kenja specifically wanted to avoid VBA.

I did have to use the "string replace" a lot to build the one cell formula when I packed the formulas from the 4 or 5 cells I had into one. The end result is totally incomprehensible, but its all in one cell. It seems the best the built-in functions can do is give a count of the quantity of numbers or alphas in a string.

If the letters are not always grouped, it won't return the correct string. Checking for that error condition using built-in functions isn't going to be easy.

It would have been a ton easier if there was a built-in function that could distinguish between alphas and numerics on its own, rather than by having to use the search for a/n specific characters.

Strangely enough, maybe something similar to the substitute error, searching with Max instead of Min returns a number that is too large by an amount equal to the number of characters in the search string.

I think that computer has Office 2013.

## RE: Excel formula help please

People usually forget to click the button to add it to the posting.

## RE: Excel formula help please

evaluate each character one time against each item in the match list, which is 50; then subtracts 4 matches to get 46.

When I use multiple digits, such as "00", "99", "19" => 18.

When I use a character, such as "A3" => 19, because 20 checks - 1 match.

For truly strange results:

## RE: Excel formula help please

WRT your 14Jul21@07:07 puzzle, I think I know how Excel is getting that result even though I don't quite understand WHY is takes the approach that it uses. (I am using Excel 2010, like you.)

If I select a single cell and enter the formula

=SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},"")

I get "3L481" like you get.

However if I select a 1x10 array of cells and enter the same expression as an Array Function I get

3L481 3L48 3L481 L481 3L81 3L481 3L481 3L481 3L41 3L481

If I now do a SUM(LEN(....)) of that 1x10 array, again as an Array Formula, I get the same 46 result as you are getting.

So your non-Array formula

=LEN(SUBSTITUTE("3L481",{"0","1","2","3","4","5","6","7","8","9"},""))

would return the length of the first element in the array, ie 5.

And your

=SUM(LEN(SUBSTITUTE("3L481",{"0","1","2","3","4","5","6","7","8","9"},"")))

returns the sum of the lengths of the ten strings

3L481 3L48 3L481 L481 3L81 3L481 3L481 3L481 3L41 and 3L481.

Clear as mud?

Me too!

## RE: Excel formula help please

Denial and 1503-44;

Thankyou both for your help. There are others here who will use these, so both will be used depending on who it is.

Im equally impressed with both.

phamENG;

If you can bend them to your needs, you are already leaving me in the dust. I cant seem to even really get the basics and I have been trying for a few years now. I have several VB codes that allow someone to pick a face, or a solid body, or whatever in NX. I cant figure out how to change it to picking a point. lol No matter what it is, it seems like the entire language changes. I have tried and tried and code just doesnt seem to sink in. Very frustrating.

IDS;

Wish I would have read further. I spent a little time trying to remember how to add a module. Been a while. Finally remembered, then found your instructions. lol

As for the rest of the conversation, I need to keep my sanity a little longer, so I am not going to try and understand it all.

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Excel formula help please

Record some simple macros, open them and step through their progression. You will soon enough come to understand VBA.

## RE: Excel formula help please

willneed it again and again in the future, and put in place a structure that will help you in that journey.Here's my structure. I have a word file (collapsible outline format). If I find a new vba routine that I'm interested in or I just developed I might put it in there. My favorite ones migrate to the top of the file. When I go back to that file, sometimes I just grab a particular routine. Other times I go back just to try to find what is the particular syntax to do something... I go back to the favorite routine that I remember includes that syntax.

You might want to put a link to this thread in that word file so you can easily come back and look at what was discussed above.

Here are 3 of my own favorite projects that you may or may not be interested in:

1 - Do you want to create a formula in a cell which will show the actual formula (rather than result) of another cell. Grab this one:

## CODE

2 - Did you ever wish excel had a "switch" function. You can get one by copying the code here: thread770-281108: switch or case statement for excel spreadsheet. Once you get used to using a switch function you'll go back and grab it every time you need it.

3 - I also have vba code that highlights the current row and column as I move my cursor around in a spreadsheet. It helps me identify the row and column headers more readily (minor benefit). The bigger benefit is that if I have two windows open on my screen trying to translate information between a spreadsheet and my other application, I can keep track of exactly where I am in the spreadsheet (excel's built in current-cell highlighting disappears when you make another window active). That's actually the one honestly more useful to me than any fancy numerical calculation because unfortunately a lot of what I do is much less exciting just translating info in and out of spreadsheets. (Let me know if you want that one)

=====================================

(2B)+(2B)' ?

## RE: Excel formula help please

TTFN (ta ta for now)

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

## RE: Excel formula help please

## RE: Excel formula help please

## CODE --> VBA

No need to have a test inside a loop when you can have the test as the Until condition.

Also does away with the need for the Exit Function statement.

## RE: Excel formula help please

## CODE --> VBA

## RE: Excel formula help please

Seriously.

I like it.

## RE: Excel formula help please

I just like minimalist code.

## RE: Excel formula help please

Your function possibly does the wrong thing if the input string does not end with a digit.

(Kenja's description of his problem does not specifically address this situation, but my reading of it is that he would want the string returned unchanged rather than a null result.)

## RE: Excel formula help please

## RE: Excel formula help please

My cell formula, as I stated, will not work, if the letters are not grouped as well.

(We built to specs!)

Yeah. 8k memory does like tiniest code possible.

I think its a good habit.

## RE: Excel formula help please

1. You copy your VBA code into Word? Weird :) (but each to their own)

2. Have you seen the new Switch function in the latest Office versions? (just the subscription version so far I think).

3. I'd be interested to see your row/column highlighter code.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

## RE: Excel formula help please

The code I want to save, reuse, reference, I put in my PERSONAL.XLSB file. Then it's just ready to use and reference, always.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

To clarify, the cells info will always start with a number, have a letter or two (always together if two) and then end with a number. At least that is the only format I have ever seen so far. Now that I have made that statement, I am certain I will find a job that is different. :)

I do thank you all for your help.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Excel formula help please

Haha, yeah I can see how it sounds weird. In the distant past I had problems with special characters from word messing up vba but that hasn't happened in a long time (I think the paste into vba editor now converts word's oddball characters into simpler text). I use word outline view for flexibility in all my notetaking. You can build hierarchical notes to whatever level indentation you want, collapse/expand them to whatever level you want, move them around when collapsed etc. If you want to search, expand all and search. It's easy to see and organize a lot of info that way imo (maybe it's just because that's the way I've always done it for last 20 years). The part where it's cumbersome is sharing info with others because if they don't view it in outline view then it can look very strange, so I often print my word outline to pdf to share it.

Iâ€™ve never taken advantage of personal.xlsb but Iâ€™ll have to look at that.

Whoops, yeah I have that available to me too. UDF no longer needed for that one!

## CODE

=====================================

(2B)+(2B)' ?

## RE: Excel formula help please

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

I'd use

Targetrather thanSelection.I like your code. Have a star.

Never used

Staticbefore. Learned something.Any reason for using

ThisRowandThisColumn? Couldn't it be...## CODE

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

I used SUM because I always do that when I have arrays and mixed type as arguments.

It accepts constants, ranges, named ranges, arrays, or cell references in any order and also ignores some errors returned by subfunctions that might be in the arguments, like it doesn't try to add text in one cell in what otherwise is a range of numbers.

Trying to add text to numbers with the + operator fails with the #VALUE! error, but SUM works.

What is returned by SUM(SUBSTITUTE())

SUM(SUBSTITUTE) RETURNED VALUE = LEN(STRING) x COUNT(ARRAY OF VALUES) - COUNT(FOUND IN STRING)

CELLS(C6).VALUE = "3LB481"

SUM(LEN(SUBSTITUTE(C6,{"X","P"},""))) = 24 when 0 of 2 array values is found in C6

SUM(LEN(SUBSTITUTE(C6,{"L","X"},""))) = 23 when 1 of 2 array values is found in C6

SUM(LEN(SUBSTITUTE(C6,{"L","B"},""))) = 22 when 2 of 2 array values is found in C6

SUM(LEN(SUBSTITUTE(C6,{"K","Z","X"},""))) = 36 when 0 of 3 array values is found in C6

SUM(LEN(SUBSTITUTE(C6,{"L","B","X"}))) = 34 when 2 of 3 array values is found in C6

## RE: Excel formula help please

Keep it going!!!

## RE: Excel formula help please

edit: I had already deduced the reason for the number. What I don't know is why Microsoft doesn't say anything about this in their documentation.

## RE: Excel formula help please

As I tried to explain in my earlier (14Jul21@08:30) post, these seemingly strange results come about as a result of Excel's "confusion" as to whether to carry out the calculations in "array mode" or "non-array mode".

When I attempted to replicate your 15Jul21@19:26 final results I got (with "3LB481" in cell C6):

SUM(LEN(SUBSTITUTE(C6,{"X","P"},""))) = 12

SUM(LEN(SUBSTITUTE(C6,{"L","X"},""))) = 11

SUM(LEN(SUBSTITUTE(C6,{"L","B"},""))) = 10

SUM(LEN(SUBSTITUTE(C6,{"K","Z","X"},""))) = 18

SUM(LEN(SUBSTITUTE(C6,{"L","B","X"},""))) = 16

where I first had to correct your fifth one to include the "" argument.

I cannot explain how your answers come to be different from these, because these seem to me to be correct given how I now understand my Excel2010 to be doing the calculations. (I suspect you might have cut&pasted from the wrong place.)

I then replaced the {squiggly bracket} character sequences with explicit ranges of cells, where I had put the elements of your character sequences into successive cells. This gave me the following results:

SUM(LEN(SUBSTITUTE($C$6,G1:H1,""))) = 6

SUM(LEN(SUBSTITUTE($C$6,G2:H2,""))) = 5

SUM(LEN(SUBSTITUTE($C$6,G3:H3,""))) = 5

SUM(LEN(SUBSTITUTE($C$6,G4:I4,""))) = 6

SUM(LEN(SUBSTITUTE($C$6,G5:I5,""))) = 5

My conclusion from all this? The use of the {squiggly bracket} character sequences in the formulae causes Excel2010 to perform at least part of its internal calculations in "array mode". This conclusion is reinforced by the fact that if I enter my above "explicitly ranged" formulae as Array Formulae, they give me the same results as I got with the {squiggly bracket} character sequences (12,11,10,18,16).

## RE: Excel formula help please

I agree with you the number of variables can be reduced, there are lots of ways to go.

Now that you make me think about it some more, we don't have to remember the color from the previous cell to clear it, we could just as well clear color on the whole sheet. (sure it wipes out all colors you may have put in there... but the old one did that too, just on a smaller scale).

So here's a new shorter version that also works. There are no extra variables.

## CODE

=====================================

(2B)+(2B)' ?

## RE: Excel formula help please

Fight, fight, fight!

I'm hoping for a Conditional Formatting knockout.

## RE: Excel formula help please

Do you mean where I found out about SUM's superpowers??? It was back in the 90's.

My brother was a CPA at Warner Bros. (He met Tom Cruise on Top Gun.) He used Excel a lot, but didn't know anything about VBA, so I did a simple program for him to automate some tax filing he was doing at the time. When I was explaining what I did, he noticed that I used the plus operator to sum a group of cells in series and suggested I use SUM(A1:A20) instead. I looked more closely at what SUM could do. SUMPRODUCT is similar, but at times gives different error notices than SUM. I have not looked into SUMPRODUCT, as I've been happy as a clam just using SUM ever since then. Where? I was in Saudi at the time.

Better yet, maybe you don't know this, apology if you do, but you can access all of Excel's built-in functions through VBA by using the "APPLICATION" reference. I dont remember how or where I found that out. I was using VB6 at the time and doing a lot of data entry and result display with Excel through DDE methods, eventually using ActiveX controls I had developed that could be pasted onto an Excel worksheet based GUI to communicate with VB6. Finally my VB6 prog could send commands and receive messages to/from XL and a 3rd party hydraulics program I use, all from the XL GUI. Then MS abandoned VB6 for VB.net. Thank You MS!

## RE: Excel formula help please

## RE: Excel formula help please

I'll accept that you don't recall how this hack came to be.

## RE: Excel formula help please

## RE: Excel formula help please

None of the nearly 100 websites I've looked at makes any such mention, but you did say "maybe." Maybe one of them has the winning lottery numbers for next week LOL.

Asking Abe Books turns up at least a dozen books by various authors and publishers with either that title or a very similar one. It's a wild goose chase.

## RE: Excel formula help please

Let me think about that one.

## RE: Excel formula help please

I like that! I, too, am sort of looking for "the theory of everything" sort of, to SUM it up.

SUM (SUMPRODUCT) is on the way.

You had asked,

Well this intrigued me, too. It seems the SUBSTITUTE function, due to the 10 element array therein, returns 10 elements, the LENth of which are {5 4 5 4 4 5 5 5 4 5} based on the first argument, and those 10 values are summed.

In my doodle sheet I created a 10 element structured table for my 10 digits and a 26 element ST for the alphabet. That seems to work eliminating the need for a literal array.

I never downloaded from the dropbox, so I don't have the total solution.

I also saw that I can use SUM() instead of SUMIF, SUMIFS, COUNTIF, COUNTIFS.

BTW, for Conditional Formatting you need

valuesnot just a cellselection. Hence a VBA solution via the SelectionChangeEvent. Native Excel spreadsheet features feature no such feature. (Somewhat like: How would you describe a misgiving about a booking at a native American resort? A reservation reservation reservation.ðŸ¤ª)Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

Then is it not SUBSTITUTE that is doing the actual iteration work? SUM is just keeping track of it all.

Reservation.Reservation.Reservation

Cute.

## RE: Excel formula help please

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

A1=27H429

B1=IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,5))),5,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,4))),4,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,3))),3,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,2))),2,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,1))),1,0)))))

C1=LEFT(A1,LEN(A1)-B1)

Hope it helps.

## RE: Excel formula help please

This result is only seen with that specific combination of SUM ( SUBSTITUTE ( {} ). There is no documentation that suggests SUBSTITUTE should or could perform an iteration)

yakpol - your method only functions for a fixed number of characters, but the use of "pre" formatting goes the extra bit and spoils the width of the comment field so it is necessary to scroll to read replies.

## RE: Excel formula help please

=LEFT(A2, MATCH(FALSE, ISNUMBER(VALUE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), 0) - 1)

Notice there's no SUBSTITUTE but there IS and

ARRAYofthe characters in A2, which is what the MID/ROW/INDIRECT functions returns to the formula.The MATCH formula returns the offset up to the first alpha character as the character count for the LEFT function.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

## RE: Excel formula help please

3L481

27H429

9SB75

Then MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) returns {"3", "L", "4", "8", "1"}

MATCH looks up FALSE from ISNUMBER(VALUE({"3", "L", "4", "8", "1"})), 0) and returns the OFFSET of the first alpha character. Subtract 1 to giv you the number of numeric characters fir the LEFT function.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

{=LEFT(A2, MATCH(FALSE, ISNUMBER(VALUE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), 0) - 1)}

via Ctrl-Shift-Enter.

Still puzzling out what Microsoft Excel developers were thinking to get this sort of formula to work.

It's interesting the behind the scenes work that is going on; I'd like to say I've learned something applicable from this,

but I'm still missing the rules for when Excel is unwinding the arrays, such as this case of converting a single string into

a virtual column of individual characters.

## RE: Excel formula help please

EDITthe formula.SELECTan expression from within the formula like...=LEFT(A2, MATCH(FALSE, ISNUMBER(VALUE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), 0) - 1)

Hit F9--this resolves the expression, displaying the value(s) produced by the expression.Hit ESC--this returns the expression to your formula.IMPORTANT!!! DO NOT MISS THIS STEP OR YOU WILL LOOSE YOUR EXPRESSION!Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

{=LEFT(A1,LEN(A1)-SUM(ISNUMBER(NUMBERVALUE(RIGHT(A1, ROW(INDIRECT("1:" & LEN(A1))))))*1))}

## RE: Excel formula help please

Who here hasn't spent a lot of time trying to figure out why a formula is working the way we expect.

I've done it waaay too many times, but never had the benefit of that tip.

=====================================

(2B)+(2B)' ?

## RE: Excel formula help please

It seems like it doesn't matter in the INDIRECT function if the argument is R1C1 format when the flag is specifically set to require

A1It also doesn't matter if it's a ROW or a COLUMN - I guess internally Excel just makes a list and ignores anything else, sometimes.

It doesn't matter if there are more entries than arguments (100 vs LEN(A2) = 5)

It doesn't matter if there is only one entry (change 100 to 1.)

The F9 trick isn't so reliable as to show what will happen.

Example:

=LEFT(A2, MATCH(FALSE, ISNUMBER(VALUE(MID(A2, COLUMN(INDIRECT(100 & ":1",

TRUE)), 1))), 0) - 1)reports that formulas cannot have more than 8192 characters. (Selecting the entire formula shows the

result "3", the left most set of numbers of "3L...")

But this:

=LEFT(A2, MATCH(FALSE, ISNUMBER(VALUE(MID(A2, COLUMN(INDIRECT(100 & ":1",TRUE)), 1))), 0) - 1)

reports "2", the count to the first non-number.

Worse, this subsection works:

COLUMN(INDIRECT("1:1",TRUE))

This doesn't:

ROW(INDIRECT("1:1",TRUE))

and neither do

ROW(INDIRECT("1:1",FALSE)) or

COLUMN(INDIRECT("1:1",FALSE))

So if one explicitly requires R1C1 and uses R1C1, it fails.

## RE: Excel formula help please

I guess you're referring to the number of array elements as entries, but that has nothing to do with the

number of function arguments.

Yes, F9 can become useless if an array is too long. But in that case, one can carefully create test data

that tests the necessary conditions and limits.

The remainder, I'm not following. I don't give a flip about R1C1 notation.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

Failing when used per the maker documentation is a huge problem.

No wonder Spoksky got it mostly right and then left.

## RE: Excel formula help please

that tests the necessary conditions and limits."

F9 was failing on a sub-part of a formula that doesn't fail. It's not an array that is too long. It's creating a FORMULA that is too long in some

failure to evaluate the FORMULA. How you are going to test the FORMULA and avoid that is seemingly far more trouble than using VBA and making the

program explicit.

These are clever, brittle hacks.

The "entries" I created what I suspected was a 1 X 100 array for 5 elements. That is A-OK. Or maybe it thinks it is one cell at R100, C1. Can't tell

because it's all undocumented behaviors.

## RE: Excel formula help please

On long and short formulas and VBA

The screenshot below may be of interest:

Thanks to all for everything, even the side-discussions :)

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

Again, you have rendered an invaluable concise solution that includes an inside-out, step by step

display that demonstrates your design of this excellent solution. Could be a tutorial.

It looks so easy and intuitive when broken down in steps. Of course it doesn't reveal the years

of knowledge and experience to know how to apply and arrange the capabilities of various functions.

Practice, practice, PRACTICE.

I think there's a limit in the length that a spreadsheet formula ought to be. It is a lot easier to design & write

VBA and a LOT easier to maintain and debug, IMNSHO. Humongous, hairy formulas are a bear to maintain and debug

let alone design. The documentation available in VBA code is valuable as well and ought to be supplied. I too often

fail in this area.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

I'm not that concerned about "undocumented behaviors."

The human kind is known for questioning and discovery. Whether a capability (a known feature) was designed in and then not documented,

is like amazing upon amazing! But like any tool, you must know and experience in use and experiment and push the envelope both of

your own capabilities and the capabilities of Excel.

I sure know that Excel has been used unwisely in the business world.

BTW, here's a link to a brief article regarding the start of MS Excel...

https://www.thedailybeast.com/microsoft-excel-the-...

I'd like to know more about how Excel was developed.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

I started with Lotus 1-2-3, then moved to Excel for Macintosh which was then re-written to run on Windows Runtime on MS-DOS. Still have

the floppy disks for the latter somewhere.

You should be concerned (but I know since you have a solution that works for you, you literally cannot be) about undocumented behaviors -

you are depending on them. They are unlikely to be designed in and just forgotten and more likely it is a flawed behavior side-effect,

like MCAS on the 737, that you are touting. Sometimes there's a reason some approach is poorly known.

Pushing the envelope makes for software that is not maintainable.

The human kind has been known to put metallic mercury, lead salts, methanol, and radium into food. Just because someone misuses something

isn't an excuse to find new ways to do so, no matter the claimed benefits of pushing an envelope.

## RE: Excel formula help please

Trimmed it down by two functions.

Removed

ISNUMBER, substituted double unary forVALUEand substitutedCOUNTforSUM.=LEFT(A7,LEN(A7)-

COUNT(--(RIGHT(A7,ROW(INDIRECT("1:"&LEN(A7)))))))Your inside-out display approach helps visualize what's happening in the formula. Thanks!

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

The new even shorter formula in action:

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

=LEFT(A2,LEN(A2)-COUNT(--(RIGHT(A2,COLUMN(INDIRECT("1:1"))))))

As I wrote, the size of the target is meaningless, but it only is reduced using COLUMN().

ROW() fails to work correctly.

## RE: Excel formula help please

Your "Shorter:" works.

However, the step-by-step as per Doug's solution, does NOT work to display the intermediate result.

Not sure what's happening. Undocumented? Undesigned?

Therefore, it is not as understandable as --(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A23)))))

Here you can actually SEE the array result. Much more useful. Much more desirable. Makes more sense.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

None of this effort to avoid VBA with cryptic and undocumented side-effects makes more sense.

## RE: Excel formula help please

3L1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456

Does F9 work to solve the problem?

## RE: Excel formula help please

I have broken down the latest variant as before.

To get the =--(RIGHT(B58,COLUMN(INDIRECT("1:1")))) to display in older versions of Excel (without dynamic arrays):

- Enter the formula

- Select the cell and the three cells to the right

- Press F2 then Ctrl-shift-enter to return it as an array

In the latest version you have to do the same thing with the COLUMN formula, because it displays as SPILL if entered with just Enter.

The reason why can be seen in the screenshot below, where I have entered =COLUMN(INDIRECT("1:1")) then pressed F9:

The column formula returns a single row array with 1856 sequential integers, followed by a couple of indecipherable symbols.

For that reason I prefer the slightly longer version, using ROW, which returns a column array only as long as required, and works for me in both latest Excel and Excel 2010.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

Using Constant Arrays and Array Expressions in Excel Formulas

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel formula help please

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

as do all the VBA examples.

I suppose as suggested earlier that a check on the length can be done and ...?

Would it be to fall back to the non-array formula or VBA for the long strings?

I tried to reproduce the "SPILL" error and to get that screen shot; when I select INDIRECT("1:1") / F9 it produces an error; formula too long

whether there is a ROW or COLUMN outside it.

Attached is a comparison of the proposed methods: https://files.engineering.com/getfile.aspx?folder=...

Note the additional test case for xxx.xxx formatting. The long formula and VBA can be changed to handle the "."; the array methods cannot be so selective

as COUNT() thinks it is a number component and canot be a separator.

As an aside - I think MS f'd up a long time ago with VBA. Instead of creating one level that only worked with document contents

and a separate level that worked outside the document they gave it the authority to do anything to the system the user was authorized

to do. As a result of malicious scripts many organizations banned all macro using spreadsheets. I suspect that remains as the root of

this problem.

Is it still the case that to see the contents of the VBA portion one has to enable the VBA content? I recall that was the case so that

VBA content cannot be seen when the option to prevent it from running is selected. I know I can use the XML viewer if I rename the file to .zip,

but that's a hassle.

## RE: Excel formula help please

Your example on the

by arraysheet, on the long value in E1...Has

more than 255 numeric characters to the rightof the alpha portion.That seems to be a limit. Documented? Not? ???

https://support.microsoft.com/en-us/office/excel-s...

A prudent Excel app designer/programmer would/should test the limits.

In this case, with 255 characters or less to the right of the alpha portion, the formula in question...

=LEFT(A7,LEN(A7)-COUNT(--(RIGHT(A7,ROW(INDIRECT("1:"&LEN(A7)))))))

...produces the desired results.

On that same sheet, you're referencing a totally numeric value that falls outside the OP's original requirements.

Naturally, it would fail.

Is there a reason for including that?

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

"Has more than 255 numeric characters to the right of the alpha portion"

Why do you think that's a limitation? That limitation is not on the page linked to.

Oh, you want to limit techniques to just the ones they are limited to working on rather than recognizing that they have really nasty flaws.

That's OK. You didn't mention those limitations before this. Why? Didn't you know? I think you don't know how the hack really works and what

the limits really are. What is your solution for the other cases?

## RE: Excel formula help please

I venture to say that I'd never reach that 255 limit in practice.

I never recall having such an unwieldy value on which to perform such surgery.

Don't know if I said this earlier, but I'm not a fan of long formulas. This one is not that long.

But I'd be more apt to write a UDF. Easier to code, maintain and understand.

"Page linked to" don't know what that is.

Yes, Excel has limitations and its a good idea to know them or know where that's documented.

Yes, I didn't know this particular 255 character limitation. But now I do.

I'd still use this COUNT(RIGHT(ROW(INDIRECT... type of solution judiciously, like any other advanced data-massaging technique.

I actually have no interest at this point in trying to divine what's happening in your other formula variants.

"I think you don't know how the hack really works..."

Lets not have a pissing contest. In half-a-year I'll be 80. I don't use Excel in the pursuit of answering questions and solving problems for an employer any more.

I enjoy Excel and its capabilities and still delight in learning a few new things if they interest me. Doug's solution interested me.

I tweaked it a bit and learned a few things along the way. It puzzles me why ROW and COLUMN behave differently.

But in the scheme of important things in my life, that's low on the totem pole.

I'm more concerned how the big tech players might be affecting our basic God-given rights not how they may have messed up some application.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

## RE: Excel formula help please

The length of that value is 268

You have 6 results from various versions of your formula in column E. 4 of them have 11 numeric characters after the alpha. The other 2 are hopeless.

268 minus 11 minus the only 2 that should be there equals 255.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel formula help please

They are that long because the array formula method fails.

That's the point of the comparison - showing that the array formula method has an ugly side-effect to it's use of a side-effect.

That's what makes the technique hopeless.

The excess characters can be greater if the length of the input string is longer. Basically, the array formula quits without

signalling an error, and returns an unwanted result.

## RE: Excel formula help please

## RE: Excel formula help please

Ok,thanks. That does suggest a potential improvement to the cell highlighter code in order to avoid wiping out existing formats in the sheet.

1 - Add the current cell highlighting within vba as conditional formatting (a condition that is always true)

2 - Clear the previous cell highlighting within vba by Deleting

onlythat previously-added conditional formatting (leaving any other formatting / conditional formatting intact).Apparently there is provision to be surgical and remove only a specific conditional format and leave the other conditional format intact.

Example of vba surgically deleting a specific conditional format here

If anyone is bored and wants to give it a try, feel free. Otherwise I'm going to try it myself this weekend.

=====================================

(2B)+(2B)' ?