Excel formula help please
9

Question

Excel formula help please

Kenja824 (Automotive)

(OP)

13 Jul 21 17:45

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.

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.

phamENG (Structural) · Answer 1 · 2021-07-13 18:14:21

You'll probably have to program a logic loop to get the results you're looking for. As far as I know, the only formulas similar to what you want are the LEFT(), MID(), and RIGHT() formulas. If you don't want to do VBA, you could do a series of IF() or IFERROR() statements in conjunction with the position formulas. So do a series of IFERROR(RIGHT(A1,i)/1,i-1) formulas in B1 so that, once the number of characters on the right includes a letter, it'll return one less. Do a total count of the characters minus that number as your number of characters (x) in a LEFT(A1,x) formula to give you the result you want.

3DDave (Aerospace) · Answer 2 · 2021-07-13 20:29:46

I don't know of a formula that can iterate - the attached sheet checks the first 10 characters and delivers the component in column B.

https://files.engineering.com/getfile.aspx?folder=98965da8-cbce-4baf-8769-5

Kenja824 (Automotive) · Answer 3 · 2021-07-13 20:58:36

Thanks for the ideas, but not exactly what I need.

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.

phamENG (Structural) · Answer 4 · 2021-07-13 21:07:39

Simply working with formulas you probably won't find a great solution. VBA is likely going to be the best solution in Excel.

Kenja824 (Automotive) · Answer 5 · 2021-07-13 21:14:12

I know and agree. The problem with VBA is I suck at it. I have been trying to learn VB Coding for years when it comes to NX Journals and I just cant seem to pick it up. My whole life, anything I have put my mind to, I could figure out, but VB just doesnt sink in. In fact, VB code, or VBA for excel as I understand it, is the reason for my quote under my signature. lol It bothers me how much I am always asking people for journals. lol

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.

Denial (Structural) · Answer 6 · 2021-07-13 21:35:11

This problem is a classic case for VBA, via a User Defined Function. Dive into it. You'll open an entire new world.

Denial (Structural) · Answer 7 · 2021-07-13 22:13:40

Couldn't help myself. Put the following code into a VBA "Module".

CODE

Option Explicit
Option Base 1

Public Function Kenja(InpStr As String) As String
'
'  Removes trailing digits from a string that comprises
'  a mixture of UPPERCASE letters and digits.
'
'  Elaborations might be required for strings that contain:
'      Lower case letters
'      Only letters
'      Characters that are neither digits not letters.
'
Dim L As Long           'Length of input string
Dim I As Long           'General purpose integer
Dim Result As String
'
L = Len(InpStr)
If L <= 0 Then
    Kenja = ""
    Exit Function
End If
'
'  Loop backwards from the input string's end until hit an uppercase letter.
'
For I = L To 1 Step -1
    If Mid(InpStr, I, 1) >= "A" And Mid(InpStr, I, 1) <= "Z" Then
        Kenja = Left(InpStr, I)
        Exit Function
    End If
Next I
'
' Input string contains no letters.
'
Kenja = InpStr
'
End Function

1503-44 (Petroleum) · Answer 8 · 2021-07-13 23:56:21

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

phamENG (Structural) · Answer 9 · 2021-07-14 00:35:12

Ken - I feel the same way. That's why I decided to just let VBA be that mysterious thing that I'll never master, but can use google to bend (mostly) to my will when the situation requires it.

Denial and 1503-44 - nicely done.

3DDave (Aerospace) · Answer 10 · 2021-07-14 02:14:10

I don't have an interest in a dropbox account. Can that file be attached here?

IDS (Civil/Environmental) · Answer 11 · 2021-07-14 02:25:04

Congratulations to 1503-44, but the VBA route is really much easier to use, and easier to understand what it does and how it does it.

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/

3DDave (Aerospace) · Answer 12 · 2021-07-14 03:09:15

Function get_left(X As String) As String
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.

IDS (Civil/Environmental) · Answer 13 · 2021-07-14 05:00:34

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

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/

3DDave (Aerospace) · Answer 14 · 2021-07-14 05:13:51

If Integer are that short then I've been duped. Not like Microsoft make their documentation so useful. https://docs.microsoft.com/en-us/dotnet/visual-bas... but no doubt that is because Microsoft has different limits for the exact same named type for VB vs VBA.

Spolsky - this is your fault. /s

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

IDS (Civil/Environmental) · Answer 15 · 2021-07-14 05:35:49

3DDAve - Sorry, I should know better than to use the first hit from an Internet search without checking it.

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.

IDS (Civil/Environmental) · Answer 16 · 2021-07-14 05:41:13

3DDave - At least we agree on Dropbox, but if you follow the link you can download to your local drive without signing in.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

3DDave (Aerospace) · Answer 17 · 2021-07-14 06:16:28

Catch my edit.

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.

IDS (Civil/Environmental) · Answer 18 · 2021-07-14 06:55:33

I thought I had posted something on my blog about the speed of Integers and Longs. I couldn't find it, but I did find:

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

Function IntCheck()
IntCheck = 24 * 60 * 30
End Function

returns an error, but

CODE -->

Function IntCheck()
IntCheck = 24 * 60 * CLng(30)
End Function

returns 43200

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

3DDave (Aerospace) · Answer 19 · 2021-07-14 07:07:55

Now - great puzzlement; as if I've had a stroke and can no longer read.

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.

1503-44 (Petroleum) · Answer 20 · 2021-07-14 07:10:12

3DDave, Are you good? How do you live without Dropbox? I hate sharing via Bluetooth.

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.

3DDave (Aerospace) · Answer 21 · 2021-07-14 07:18:01

I use the following which uploads to Eng Tips.

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

3DDave (Aerospace) · Answer 22 · 2021-07-14 08:02:33

=SUM(LEN(SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},""))) => 46 seems to be:

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:

=SUM(100+LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) => 1090
=SUM(100-LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) =>  910 
=SUM(10- LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) =>   10
=SUM(1-  LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) =>  -80
=SUM(0-  LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) =>  -90

Denial (Structural) · Answer 23 · 2021-07-14 08:30:21

3DDave,

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!

Kenja824 (Automotive) · Answer 24 · 2021-07-14 13:03:03

Got to work today and found quite the discussion going on in this thread. lol

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.

1503-44 (Petroleum) · Answer 25 · 2021-07-14 13:29:30

Kenja,

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

electricpete (Electrical) · Answer 26 · 2021-07-14 15:38:59

If I can give one piece of advice on VBA, it is simply to anticipate that you will need 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

Function GetFormula(Cell)
   GetFormula = Cell.Formula
End Function

Paste it into your module. Use it in your spreadsheet =getformula(A3) as a feature to help document the logic of your spreadsheet (along with named ranges, another great tool for readability).

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)' ?

IRstuff (Aerospace) · Answer 27 · 2021-07-14 16:12:13

I second the notion of using the macro recorder; most often, I record the sequence of things I want the macro to do and then slog through the recording to generalize it.

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

1503-44 (Petroleum) · Answer 28 · 2021-07-14 16:27:41

Yeah. Nothing better than eating one tiny "byte" at a time, immediately relevant to what you want to accomplish at the moment.

MintJulep (Mechanical) · Answer 29 · 2021-07-14 17:01:04

My variation, inspired by 3DDave's VBA function.

CODE --> VBA

Public Function Kenja(S As String) As String

Dim Countdown As Long

Countdown = Len(S)

Do Until InStr("0123456789", Mid(S, Countdown, 1)) = 0
    Countdown = Countdown - 1
    Kenja = Left(S, Countdown)
Loop

End Function

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.

MintJulep (Mechanical) · Answer 30 · 2021-07-14 17:20:33

And in case the input is a number.

CODE --> VBA

Public Function Kenja(S As Variant) As String

Dim Countdown As Long

If Not IsNumeric(S) Then

    Countdown = Len(S)
    
    Do Until InStr("0123456789", Mid(S, Countdown, 1)) = 0
        Countdown = Countdown - 1
        Kenja = Left(S, Countdown)
    Loop
    
Else
    Kenja = ""
End If

End Function

1503-44 (Petroleum) · Answer 31 · 2021-07-14 17:23:57

Do I detect a Z80 programmer?

Seriously.
I like it.

MintJulep (Mechanical) · Answer 32 · 2021-07-14 17:45:49

Maybe once during a lab course in university, long ago.

I just like minimalist code.

Denial (Structural) · Answer 33 · 2021-07-14 21:27:17

MintJulep

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

MintJulep (Mechanical) · Answer 34 · 2021-07-14 21:55:38

It's implied that they will always be numbers after letters.

1503-44 (Petroleum) · Answer 35 · 2021-07-14 22:22:32

It is defined so.
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.

IDS (Civil/Environmental) · Answer 36 · 2021-07-14 23:30:58

Pete:
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/

3DDave (Aerospace) · Answer 37 · 2021-07-15 00:13:31

1503-44 - do any of the sub-elements work for you as I have described and if so, how did you know that they would work in the exact combination you used? Without the use of the SUM() wrapper they do not produce usable answers for me.

SkipVought (Aerospace) · Answer 38 · 2021-07-15 12:44:28

Here's my structure.

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!}

Kenja824 (Automotive) · Answer 39 · 2021-07-15 13:16:53

Please forgive me, but I find it amusing how much of a discussion this has turned into. To be honest, I read through it and dont understand a quarter of what is said. lol

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.

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.

electricpete (Electrical) · Answer 40 · 2021-07-15 14:25:04

Quote (skip)
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.

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

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.

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

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

Quote (Doug)
3. I'd be interested to see your row/column highlighter code.

For the benefit of other readers (Doug already knows), code below goes into the particular worksheet vba area rather than into a module.
It could no doubt be improved for readability or style. You could use only one set of row/col variables, which are updated in between clearing the "last" selection and highlighting the current "this" selection, rather than using separate "last" and "this" variables like shown below. Or if you want to keep both sets of variables then you could move initialization of thisRow and thisCol to the beginning and update of lastRow and lastCol to the end. Maybe thisRow and thisCol should initialize based on Target instead of Selection. I'm not sure which is most elegant (open to comment), but it works so I'm happy

CODE

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Current Cell row/column highlighter
' update 07152021
' Usage note - if for some reason a cell remains highlighted
'     ... which is not the current cell, then fix it by
'     ... clicking on that cell and then clicking on any other cell

Static lastRow, lastColumn ' Holds the value from last call to sub (previous cell):

If lastColumn <> "" Then ' Clear color from the last cell:
    With Columns(lastColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(lastRow).Interior
        .ColorIndex = xlNone
    End With
End If

thisRow = Selection.Row  ' Update variables to current cell:
thisColumn = Selection.Column
lastRow = thisRow
lastColumn = thisColumn

With Columns(thisColumn).Interior  ' Color highlight the current cell:
    .ColorIndex = 6 ' yellow (vbYellow doesn't work)
    .Pattern = xlSolid
End With
With Rows(thisRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

=====================================
(2B)+(2B)' ?

SkipVought (Aerospace) · Answer 41 · 2021-07-15 16:39:09

(TB)+(TB)' CONJESTION

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought (Aerospace) · Answer 42 · 2021-07-15 17:05:29

Just a technical nit picky suggestion. 🤓

I'd use Target rather than Selection.

I like your code. Have a star.

Never used Static before. Learned something.

Any reason for using ThisRow and ThisColumn? Couldn't it be...

CODE

lastRow = Target.Row  ' Update variables to current cell:
lastColumn = Target.Column

With Columns(lastColumn).Interior  ' Color highlight the current cell:
    .Color = vbYellow
    .Pattern = xlSolid
End With
With Rows(lastRow).Interior
    .Color = vbYellow
    .Pattern = xlSolid
End With

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

1503-44 (Petroleum) · Answer 43 · 2021-07-15 19:26:45

3DDave,
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

IFRs (Petroleum) · Answer 44 · 2021-07-15 22:44:37

This was (is) a GREAT thread. I learned a lot (I have been looking forward to the day I get to use "SWITCH").

Keep it going!!!

3DDave (Aerospace) · Answer 45 · 2021-07-15 23:46:11

1503-44, that's fine that you found that out. Where did you find that out?

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.

Denial (Structural) · Answer 46 · 2021-07-16 00:38:02

1503-44 (and others), with advance apologies for my inability speak proper Excelese.

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

electricpete (Electrical) · Answer 47 · 2021-07-16 01:22:47

Quote (Skip V)
Any reason for using ThisRow and ThisColumn? Couldn't it be...

That cleans up my confusion with using vbYellow...I have to use it with color, not colorindex.
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

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Current cell row/col highlighter
' update 07152021a - this version wipes out all colors
' Usage note - if for some reason a cell remains highlighted
'     ... which is not the current cell, then fix it by
'     ... clicking on that cell and then clicking on any other cell

Target.Parent.Cells.Interior.Color = xlNone  ' wipes out all colors in sheet, including previous highlight

With Columns(Target.Column).Interior  ' Color highlight the current cell:
    .Color = vbYellow
    .Pattern = xlSolid
End With
With Rows(Target.Row).Interior
    .Color = vbYellow
    .Pattern = xlSolid
End With

End Sub

=====================================
(2B)+(2B)' ?

3DDave (Aerospace) · Answer 48 · 2021-07-16 02:03:03

Does this work with Conditional Formatting?

Fight, fight, fight!

I'm hoping for a Conditional Formatting knockout.

1503-44 (Petroleum) · Answer 49 · 2021-07-16 07:36:45

Quote (3DDave)
1503-44, that's fine that you found that out. Where did you find that out?

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

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!

1503-44 (Petroleum) · Answer 50 · 2021-07-16 07:41:31

Denial, Sorry. I was copying and pasting from my spreadsheet to here. Might be an error there, or two...

3DDave (Aerospace) · Answer 51 · 2021-07-16 08:04:22

Yes, that's what SUM() is designed to do. It's not obvious that it is intended to iterate the entire contents with an impromptu array inside a SUBSTITUTE() function appearing somewhere in those contents. It iterates constants at the same time. But it doesn't always do it. It seems like it only does it when SUBSTITUTE() is used. For example, =SUM(1+SUM({1,2,3,4})) does not iterate.

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

1503-44 (Petroleum) · Answer 52 · 2021-07-16 08:31:48

Oh, combining SUM and SUBSTITUTE. Might have seen something I saw in "Excel for Scientists and Engineers"? A reference I was using a lot around that time, but I can't be sure. If it only iterates with SUBSTITUTE, I didnt know that, search Google for both those terms together and maybe some documentation will show up.

3DDave (Aerospace) · Answer 53 · 2021-07-16 09:20:28

I guess I'm not $80 curious enough to see if Excel for Scientists and Engineers mentions it.

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.

1503-44 (Petroleum) · Answer 54 · 2021-07-16 13:25:46

Winning lottery numbers for next week...
Let me think about that one.

SkipVought (Aerospace) · Answer 55 · 2021-07-16 15:06:16

3DDave, the question box, 😉

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,

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

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 values not just a cell selection. Hence a VBA solution via the SelectionChange Event. 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!}

1503-44 (Petroleum) · Answer 56 · 2021-07-16 16:31:01

"It seems like it only does it when SUBSTITUTE() is used."
Then is it not SUBSTITUTE that is doing the actual iteration work? SUM is just keeping track of it all.

Reservation.Reservation.Reservation
Cute.

SkipVought (Aerospace) · Answer 57 · 2021-07-16 16:49:48

The iteration is the result of the {array} (literal or referenced) and SUM sums the results of what's performed on the array.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

yakpol (Structural) · Answer 58 · 2021-07-16 17:32:28

Here's a solution without using VBA, just formulas. It works if the number of digits right of the last letter is limited. In the presented formula it's assumed 5, but can be increased if needed. The formula in B1 returns the number of digits past the last symbol, the formula in C1 returns desired string.

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.

3DDave (Aerospace) · Answer 59 · 2021-07-16 18:41:01

That array is not a supported or mentioned feature in Excel documentation.

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.

SkipVought (Aerospace) · Answer 60 · 2021-07-16 19:51:54

Here's an example that returns the numeric characters in a string up to the first non-numeric character...

=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 ARRAY of the 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!}

3DDave (Aerospace) · Answer 61 · 2021-07-16 20:52:32

Skip - Is it an array in A2 like ={"1","2","3","4...}? Where is the text that is being parsed? Is it also in A2?

SkipVought (Aerospace) · Answer 62 · 2021-07-16 21:25:51

A2 and following contain...

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!}

3DDave (Aerospace) · Answer 63 · 2021-07-16 22:08:00

Skip - I just found that formula you gave is required to be an array formula, so

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

SkipVought (Aerospace) · Answer 64 · 2021-07-16 22:20:29

What I find helpful in figuring out how a formula works is...

EDIT the formula.
SELECT an 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!}

yakpol (Structural) · Answer 65 · 2021-07-16 22:58:17

Skip, thanks for showing this technique. Implementing it I came up with this array formula. It looks weird, but works:
{=LEFT(A1,LEN(A1)-SUM(ISNUMBER(NUMBERVALUE(RIGHT(A1, ROW(INDIRECT("1:" & LEN(A1))))))*1))}

electricpete (Electrical) · Answer 66 · 2021-07-16 23:15:24

Quote (skip)
What I find helpful in figuring out how a formula works is...
[paraphrased: while editing, highlight subexpression and press F9 to see it evaluated, then escape when you're done]

LPS for that !!!
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)' ?

3DDave (Aerospace) · Answer 67 · 2021-07-17 01:53:39

More Excel hocus-pocus.

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

SkipVought (Aerospace) · Answer 68 · 2021-07-17 03:16:16

Quote (3DDave)
It doesn't matter if there are more entries than arguments

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!}

3DDave (Aerospace) · Answer 69 · 2021-07-17 03:52:19

Skip - neither did the MS programmers. What I'm getting at is that if the software is used according to the MS documentation - IT FAILS.

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

No wonder Spoksky got it mostly right and then left.

3DDave (Aerospace) · Answer 70 · 2021-07-17 04:03:06

Skip, just to mention - "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."

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.

IDS (Civil/Environmental) · Answer 71 · 2021-07-17 04:52:32

I have extracted the bits of this thread related to the original question in a blog post:

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/

SkipVought (Aerospace) · Answer 72 · 2021-07-17 14:08:37

@Doug,

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!}

SkipVought (Aerospace) · Answer 73 · 2021-07-17 14:39:29

@3DDave,

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!}

3DDave (Aerospace) · Answer 74 · 2021-07-17 15:58:54

Funny Skip,

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.

Answer 75

[Post Deleted]

SkipVought (Aerospace) · Answer 76 · 2021-07-17 19:16:36

@Doug,

Trimmed it down by two functions.
Removed ISNUMBER, substituted double unary for VALUE and substituted COUNT for SUM.
=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!}

IDS (Civil/Environmental) · Answer 77 · 2021-07-18 00:57:58

Skip = sigh, I suppose I'll have to update my blog post now :)

The new even shorter formula in action:

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

3DDave (Aerospace) · Answer 78 · 2021-07-18 01:15:36

Shorter:

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

SkipVought (Aerospace) · Answer 79 · 2021-07-18 02:38:36

@3DDave,

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!}

3DDave (Aerospace) · Answer 80 · 2021-07-18 02:47:47

And here I thought you loved discovering new things.

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

3DDave (Aerospace) · Answer 81 · 2021-07-18 04:13:31

Try the hack on this:

3L1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456

Does F9 work to solve the problem?

IDS (Civil/Environmental) · Answer 82 · 2021-07-18 05:22:29

3DDave - I agree that VBA is better for these things, but not everybody does, and looking at how these formulas work is both interesting and useful.

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/

IDS (Civil/Environmental) · Answer 83 · 2021-07-18 05:26:40

Also there is some useful information on using "Constant Arrays" and "Array Expressions" at the Fast Excel blog:

Using Constant Arrays and Array Expressions in Excel Formulas

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

SkipVought (Aerospace) · Answer 84 · 2021-07-18 05:39:24

Dave, what is your point?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

3DDave (Aerospace) · Answer 85 · 2021-07-18 10:17:10

Doug - neither of the short methods works for a long input; the original weird array from 1503-44 but-not-an-array-formula does,
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.

SkipVought (Aerospace) · Answer 86 · 2021-07-18 18:44:50

@3DDave,

Your example on the by array sheet, on the long value in E1...

Has more than 255 numeric characters to the right of 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!}

Answer 87

[Post Deleted]

3DDave (Aerospace) · Answer 88 · 2021-07-18 20:05:42

Skip,

"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?

SkipVought (Aerospace) · Answer 89 · 2021-07-19 01:26:43

I never mentioned a litany of Excel limitations. Neither does anyone else UNLESS the limitation is relevant to the immediate issue.
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!}

3DDave (Aerospace) · Answer 90 · 2021-07-19 03:26:36

What does 255 have to do with anything in my spreadsheet? It doesn't affect any of the calculations.

SkipVought (Aerospace) · Answer 91 · 2021-07-19 04:15:38

Check your results for the value in E1.
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!}

3DDave (Aerospace) · Answer 92 · 2021-07-19 05:06:32

There is no limitation of 255 characters on the contents in a cell or the result.

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.

3DDave (Aerospace) · Answer 93 · 2021-07-19 07:39:18

To answer the previous question about the highlighting - Conditional formatting has a higher priority.

electricpete (Electrical) · Answer 94 · 2021-07-19 14:00:11

> To answer the previous question about the highlighting - Conditional formatting has a higher priority.

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 only that 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)' ?

Answer 95

[Post Deleted]

Come Join Us!

Posting Guidelines

Excel formula help please 9