Excel formula help please 9

[Kenja824]

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

http://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.

 

[3DDave]

Try the hack on this:

3L1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456

Does F9 work to solve the problem?

 

[IDS]

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]

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]

Dave, what is your point?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

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/getfi...9e-8215-9b6939d0d16d&file=left_component.xlsm

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]

@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...d-limits-1672b34d-7043-467e-8e27-269d656771c3

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...
[tt]=LEFT(A7,LEN(A7)-COUNT(--(RIGHT(A7,ROW(INDIRECT("1:"&LEN(A7)))))))[/tt]
...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

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]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

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]

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

 

[electricpete]

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